Help with if then statements

brent chadwick · #1 07-15-2015, 04:27 AM

If I run the macro in the doc you sent me, it works fine. If I change the "BkMk" to anything else if gives me the "Bookmark:XXX not found", and does not insert a form field. What am I missing here? Thanks-

macropod · #2 07-15-2015, 04:32 AM

Quote:

Originally Posted by brent chadwick

If I run the macro in the doc you sent me, it works fine. If I change the "BkMk" to anything else if gives me the "Bookmark:XXX not found", and does not insert a form field. What am I missing here?

Please have another read of what I said:

Quote:

Originally Posted by macropod

You can use another name, of course, but you need to use the same name in both the document and the macro.

brent chadwick · #3 07-15-2015, 04:58 AM

If I use it in the doc I'm working, and give it the bookmark of the drop-down menu, it erases the drop-down menu. If I give it the bookmark of any other form field in the doc, it erases and replaces that form field. So it is not giving an additional (new) form field. Thanks-

macropod · #4 07-15-2015, 05:33 AM

Quote:

Originally Posted by brent chadwick

If I use it in the doc I'm working, and give it the bookmark of the drop-down menu, it erases the drop-down menu.

I wouldn't expect anything else! The dropdown's bookmark should most certainly be referenced in the code.

Quote:

If I give it the bookmark of any other form field in the doc, it erases and replaces that form field. So it is not giving an additional (new) form field.

Again, that's exactly what one should expect. As I said in a previous post:

Quote:

The bookmark named 'BkMk' is one you create in the document, via Insert|Bookmark

Obviously, if you're chnaging the macro to use a bookmark already assigned to another formfield, you're not using one you've created via this process.

brent chadwick · #5 07-25-2015, 10:59 AM

With a few minor mods this code works perfectly-Thanks. Have you or anyone you know created a pull-down menu in the Quick Access Toolbar?

macropod · #6 07-25-2015, 02:41 PM

Quote:

Originally Posted by brent chadwick

Have you or anyone you know created a pull-down menu in the Quick Access Toolbar?

AFAIK that's not possible. Some items can be added via VBA, but not a dropdown.

macropod · #7 07-17-2015, 05:44 AM

Without seeing your code, plus the document you're trying to populate, you can't really expect anyone to give specific advice on what's wrong...

As for VBA programming books, I can't give specific recommendations - all my VBA skills are self-taught (mostly through helping people in forums such as this).

brent chadwick · #8 07-17-2015, 05:51 AM

I can send you whatever you like.

macropod · #9 07-17-2015, 03:06 PM

You could attach a document to a post, just as I did earlier in this thread, with some representative data (delete anything sensitive). You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

brent chadwick · #10 07-18-2015, 04:37 AM

Here's the doc that has all of the text that the macro needs to produce. The years (1790-1940) are in a drop-down menu. The regular non-conditional form fields are the grey highlighted ones. There are 6 conditional form fields depending on the chosen year highlighted in yellow. There is one conditional drop-down menu (AorB).
What is done-
The year drop-down. All of the text and conditional text. All of the bookmarks for the conditional form fields.
What I need help on-
The conditional form fields and the one conditional drop-down menu.

Here is the drop-down exit macro which I am assuming where the additional code needs to go. I have tried to write it and failed miserably.

Code:

Sub LockDD1()
'
' Exit macro
'
'

Dim Prot As Variant, BmkRng As Range
Const BmkNm As String = "Column" ' The bookmark name
With ActiveDocument
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect Password:=Pwd
  End If
  If .Bookmarks.Exists(BmkNm) Then
    Set BmkRng = .Bookmarks(BmkNm).Range
    If .FormFields("DD1").Result = "1790" Then
      .FormFields.Add BmkRng, wdFieldFormTextInput
      BmkRng.FormFields(1).TextInput.EditType wdRegularText, "X,"
      Else
      BmkRng.Delete
End If
    .Bookmarks.Add BmkNm, BmkRng
  Else
    MsgBox "Bookmark: " & BmkNm & " not found."
  End If
  .Protect Type:=Prot, NoReset:=True
End With
Set BmkRng = Nothing

     Application.ScreenUpdating = False
    If ActiveWindow.View.Type = wdPageView Then
        ActiveWindow.ActivePane.View.Type = wdNormalView
    Else
        ActiveWindow.View.Type = wdPageView
    End If
    If ActiveWindow.ActivePane.View.Type = wdNormalView Then
        ActiveWindow.ActivePane.View.Type = wdPageView
    Else
        ActiveWindow.ActivePane.View.Type = wdNormalView
    End If
    End Sub

BTW kudos to you and being self taught-I too am self taught but at least a couple of light-years behind-Thanks

macropod · #11 07-18-2015, 06:15 PM

There's no fields of any kind in your document, so it's far from apparent what you're expecting. All I can see is plain text, some of which has grey highlighting and some of which has yellow highlighting.

Does you real document have all these paragraphs, or are the ones in your attachment simply the conditional variations on a single paragraph?

You say you've done "The year drop-down. All of the text and conditional text. All of the bookmarks for the conditional form fields.", but none of these is present in your document. Nor is the code you're using to drive the process - the macro you posted certainly couldn't do any more than conditionally add a single formfield; it can't even do conditional text. As some of your paragraphs have additional strings, such as 'population schedule,' 'col.', 'line' 'dwelling' and 'family', I'm guessing they're your conditional text, but there's no indication of whether that's so.

brent chadwick · #12 07-19-2015, 01:20 PM

Here's the doc that the macros are in, there is no text in it because the macro creates all of the text that the other doc has-

macropod · #13 07-19-2015, 11:58 PM

Try the attached. For your template, you will require all of the included code instead of what you now have for your 'TabbedCensus1790To1940Macro' and 'LockDD1' macros. I've also commented-out the .ExitMacro = "UnlinkFields" line in the code for testing purposes (plus that macro isn't in the code I'm returning).

As you will see, a complete re-write of your code was required. The code should now give you a good idea of how to use macros for conditional text & formfield insertion at nominated bookmarks. Note, too, that I've eliminated all of your IF fields. Given the need to conditionally insert multiple formfields, it didn't make sense having some of the processing done that way and the balance via macro coding when the latter handles the lot quite seamlessly.

brent chadwick · #14 07-20-2015, 01:17 PM

Whoa! there's a lot to digest here! I would ask you to explain the why and how but that would take too long. Your help is much appreciated- Thanks-

brent chadwick · #15 07-21-2015, 03:53 AM

Couple of things:
1) I need to take out the 1890 as there is no census for that decade.
2) When I create a toolbar for this doc both of the macros do not show up in the commands section. Why is that? Thanks-

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple If statements,	Jamal NUMAN	Excel	16	11-15-2022 11:59 PM
Countif statements	Alaska1	Excel	5	02-05-2015 07:55 PM
Using IF statements	stuwoolf	Excel	2	01-10-2015 01:58 PM
count if statements	Alaska1	Excel	1	05-14-2014 08:21 AM
IF statements that shifts to right.	kent	Excel	0	01-19-2006 02:23 PM