none
If Statement with Bookmarks - Word VBA RRS feed

  • General discussion

  • I'm a true beginner at VBA, so please excuse my lack of vocabulary. 

    I'm using merge fields from excel to populate a 'paragraph' in word. Here it is. 


    I've bookmarked the entire selection as SF1. Also, I've bookmarked the mail merge field <<SF1Q>> as SF1R. What I'm trying to accomplish is to delete SF1 entirely if SF1R is equal to 0. I'm struggling with Declaring (Dim) SF1R as an Interger or Long. I'm not sure if it is required. I'm attempting to create an if statement that looks like this: If ActiveDocument.Bookmarks("SF1R") = 20 Then Delete SF1 and I'm getting all kinds of errors! If someone can let me know what kind of declarations are required and help in constructing my if statement it would be greatly appreciated!

    Tuesday, July 7, 2020 7:30 PM

All replies

  • 084113 Exterior Storefront – Total «SF1T» Labor «SF1L»
     «SF1Manu» «SF1Name» | «SF1Finish» with «SF1Year» Year Warranty | 1” Insulated Clear SolarBan70XL | 2 Beads of Caulk
    «SF1Q» Total Openings | Approx. «SF1SF» SF

    here is the selection

    Tuesday, July 7, 2020 7:31 PM
  • Based on your enquiry, the following should work i.e. if the value of bookmark SF1R = 20 then delete Bookmark SF1

    If ActiveDocument.Bookmarks.Exists("SF1R") = True Then
            If Val(ActiveDocument.Bookmarks("SF1R").Range.Text) = 20 Then
                If ActiveDocument.Bookmarks.Exists("SF1") = True Then
                    ActiveDocument.Bookmarks("SF1").Range.Text = ""
                End If
           End If
    End If

    If this is mailmerge then you could instead conditionally merge the whole section based on the value of the field without the need for a macro.


    Graham Mayor - Word MVP
    www.gmayor.com



    Wednesday, July 8, 2020 2:58 AM
  • Amazing! I would never have figured that. Thank you so much. If you give a mouse a cookie...

    I've added another bookmark to make certain of the deletion, but my customer may not want the price to be shown to theirs. I've replaced SF1R with SF1Q for quantity. I've added SF1P to represent price. The code is as follows, but should the customer not want the price to be shown, SF1P will be effectively deleted and I'd have to personalize the macro for this individual. It seems to me that the if exists statement would take care of it. I'm assuming that if the bookmark (SF1P) doesn't exist, then the marco would just move to deleting SF1 because SF1Q =0, but this isn't the case. What must I add?

    If ActiveDocument.Bookmarks.Exists("sf1q") = True Then
            If Val(ActiveDocument.Bookmarks("sf1q").Range.Text) = 0 Then
                If ActiveDocument.Bookmarks.Exists("sf1p") = True Then
                    If Val(ActiveDocument.Bookmarks("sf1p").Range.Text) = 0 Then
                        If ActiveDocument.Bookmarks.Exists("SF1") = True Then
                            ActiveDocument.Bookmarks("SF1").Range.Text = ""
                        End If
                    End If
                End If
            End If
        End If

    Wednesday, July 8, 2020 4:25 PM
  • Oh and to be clear, the 20 was just the placeholder during my futile attempts earlier. The 20 is effectively 0. 
    Wednesday, July 8, 2020 4:27 PM
  • I am having difficulty getting my head around that. Can you make the document available so that we can see what each of the bookmarks encloses?

    Graham Mayor - Word MVP
    www.gmayor.com

    Thursday, July 9, 2020 4:03 AM
  • https://www.dropbox.com/s/fuxad8bjn6hhanc/08Proposal%20-%20American%20Glazing%20-%2082.docm?dl=0

    Couldn't find an attachment button!

    Basically, I'm looking to make the macro as versatile as possible to be available to variables my customers may prefer. SF1Price and SF1Quantity are to be the 2 if statement checks. If the both of them are 0, then SF1 is to be deleted. Id like a macro that works when SF1P doesn't exist and SF1Q is the only if statement check.  

    https://social.msdn.microsoft.com/Forums/office/en-US/0ec48ba4-98b9-41e7-a2ab-c3deb11f63bc/excel-programs-to-protect-with-license?forum=worddev

    also, I checked out your website. Very cool. I tipped you $10. If you're aware of excel licensing companies, you're help is very much appreciated. 

    Friday, July 10, 2020 10:46 PM
  • SF1P is a number formatted for price { MERGEFIELD SF1P \#$,0}

    SF1Q is just a number { MERGEFIELD SF1Q}

    SF1 is the Following

    084113 Exterior Storefront – Total «SF1T» Labor «SF1L»
     «SF1Manu» «SF1Name» | «SF1Finish» with «SF1Year» Year Warranty | 1” Insulated Clear SolarBan70XL | 2 Beads of Caulk
    «SF1Q» Total Openings | Approx. «SF1SF» SF

    Friday, July 10, 2020 10:49 PM
  • SF1Q is within the bookmark SF1, so if you delete SF1, then SF1Q will be deleted with it.

    SF1P and the field { MERGEFIELD SF1P \#$,0} do not appear in your document so we are no clearer on how that operates.

    The document is saved as DOCM format but does not contain any code. Did you intend that the code would go here?


    Graham Mayor - Word MVP
    www.gmayor.com



    Saturday, July 11, 2020 1:08 PM