make buttons invisible

[hellohello1]

I have a Microsoft Word 2003 document that has mail merge fields with Excel as its data source.

I created a few command buttons on my main form. One button allows the user to select the data source, one allows the user to select recipients and the third Merges the data and creates a new document.

I would like those command buttons to *not* appear on the newly created documents.

Here is my code for the button that creates the new document:

Private Sub cmdMergeDocument_Click()
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True

Me.cmdSelectDataSource.Visible = False 'this doesn't work!
Me.cmdMergeDocument.Visible = False 'this doesn't work!
Me.cmdSelectRecipients.Visible = False 'this doesn't work!

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

'now show the buttons again on the main form
Me.cmdSelectDataSource.Visible = True 'this doesn't work!
Me.cmdMergeDocument.Visible = True 'this doesn't work!
Me.cmdSelectRecipients.Visible = True 'this doesn't work!
End Sub

I get 'Object doesn't support this property or method'.

I am able to do: cmdSelectDataSource.Enabled = False, but it's not what I need. I need the buttons invisible, not disabled.

I don't want to make a UserForm because it's a 20 page document with over 100 merge fields. I just need to make these 3 buttons invisible.

Help!
Thanks,

 

[hellohello1]

If no one can help me with making the buttons invisible, I was thinking of putting the buttons on the first page.

Then, is there a way to do the merge and start the merge at page 2?

Thanks,

 

[SkipVought]

If you checked the properties for a command button, there is no visible property in MS Word.

Add a UserForm. Make it modeless, so that the use can interact with the document.

You can start a merge anywhere you choose. But it includes ALL pages in the template.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

As Skip points out, ActiveX controls in the document do not - as opposed to controls on a userform, which DO - have a .Visible property.

Do you have IntelliSense on? If it is on, then when you typed:

Me.cmdMergeDocument.

you would have seen that Visible was NOT a possible option.

You can not use Visible for a control in the document, but you CAN resize controls. This may, or may not, be viable for you, but you could dynamically resize the controls to a single pixel, which makes them vitually invisible...although yes, they would still be printed...just really really small.

Gerry

 

[hellohello1]

thanks for writing.

fumei, I tried Me.cmdMergeDocument.Width = 1 and it works!

The only weird thing is setting it back to normal on the main document. I want my height=45 and my width=117.

But for some reason, if my code lists the height first:
Me.cmdSelectDataSource.height =45
Me.cmdSelectDataSource.width=117
then both the height and the width are 117.

If i list the width first:
Me.cmdSelectDataSource.width=117
Me.cmdSelectDataSource.height=45
then both the height and the width are 45.

It's seems to only consider the last command and then apply that value to both height and width?

Here is my code that shows both the height and width as 45:

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource

'make the buttons small
Me.cmdMergeDocument.Width = 1
Me.cmdSelectDataSource.Width = 1
Me.cmdSelectRecipients.Width = 1

.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Execute Pause:=False
End With

'now re-size the buttons again
Me.cmdSelectDataSource.Width = 117
Me.cmdSelectDataSource.Height = 45

Me.cmdSelectRecipients.Width = 117
Me.cmdSelectRecipients.Height = 45

Me.cmdMergeDocument.Width = 117
Me.cmdMergeDocument.Height = 45

How can i make the width=117 and the height=45?

Weird.

 

[fumei]

1. 45 seems rather large as a height...but that could be because of your resolution.

2. it works fine for me.

Private Sub CommandButton3_Click()
   Select Case CommandButton1.Height
      Case 24
         CommandButton1.Height = 0.75
         CommandButton1.Width = 0.75
         CommandButton2.Height = 0.75
         CommandButton2.Width = 0.75
      Case 0.75
         CommandButton1.Height = 24
         CommandButton1.Width = 72
         CommandButton2.Height = 24
         CommandButton2.Width = 72
      Case Else
         MsgBox "something wrong." & vbCrLf & _
               CommandButton1.Height
   End Select
End Sub

Clicking commandbutton3 makes comandbutton1 & 2 either:

wee (0.75 in both .Height and .Width)...OR
"normal" (Height = 24, Width = 72)

NOTE! the numeric values are (like everything else in Word) totally dependent on the printer driver. For the code above, I originally had:

Private Sub CommandButton3_Click()
   Select Case CommandButton1.Height
      Case 24
         CommandButton1.Height = 1
         CommandButton1.Width = 1
         CommandButton2.Height = 1
         CommandButton2.Width = 1
      Case 1
         CommandButton1.Height = 24
         CommandButton1.Width = 72
         CommandButton2.Height = 24
         CommandButton2.Width = 72
      Case Else
         MsgBox "something wrong." & vbCrLf & _
               CommandButton1.Height
   End Select
End Sub

but would ALWAYS get the Case Else...because while the VBA instruction is CommandButton1.Height = 1, the actual value imposed by the printer driver was 0.75...so testing for 1 always failed, as it was NOT 1.

Which is why I did the messagebox for Case Else, and it displayed the actual value. I then went back and changed the code:

CommandButton1.Height = 1

to

CommandButton1.Height = 0.75

as that was what Word was doing anyway. However, once corrected for the real values, it worked fine.

Gerry

 

[hellohello1]

fumei,
thanks for writing.

I tried your way, but the code below still gives me a height and width of both 117.

Could it be related to Autosize? The Autosize property for those 3 command buttons is set to True. I tried setting them to False, but that didn't change anything.

I was able to get around this problem by choosing a width of 75, since having both a height and width of 75 is acceptable to my document.

Something is forcing my code to create a box, instead of a rectangle. weird.

Private Sub cmdMergeDocument_Click()
On Error GoTo Err_error_Click
'merge document-step 3

If MsgBox("Are you sure you want to create a new document(s)?", vbOKCancel, "Create documents") = vbCancel Then
Exit Sub
End If

'first check if the document if protected. If so, unprotect it. Opening the document triggers code to protect it (see the Document_Open() event)
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect "hi"
End If


With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource

'make the buttons small
Me.cmdMergeDocument.Width = 1.3
Me.cmdSelectDataSource.Width = 1.3
Me.cmdSelectRecipients.Width = 1.3

.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Execute Pause:=False
End With

'now re-size the buttons again
Select Case Me.cmdSelectDataSource.Height
Case 45
Me.cmdMergeDocument.Height = 1.3
Me.cmdMergeDocument.Width = 1.3

Me.cmdSelectDataSource.Height = 1.3
Me.cmdSelectDataSource.Width = 1.3

Me.cmdSelectRecipients.Height = 1.3
Me.cmdSelectRecipients.Width = 1.3


Case 1.3
Me.cmdMergeDocument.Height = 45
Me.cmdMergeDocument.Width = 117

Me.cmdSelectDataSource.Height = 45
Me.cmdSelectDataSource.Width = 117

Me.cmdSelectRecipients.Height = 45
Me.cmdSelectRecipients.Width = 117

Case Else
MsgBox "something wrong: " & Me.cmdSelectDataSource.Height
End Select

'protect document again
ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="hi"

Exit_cmdMergeDocument_Click:
Exit Sub

Err_error_Click:
MsgBox Err.Description
Resume Exit_cmdMergeDocument_Click

End Sub

Thanks!

 

[fumei]

Could it be related to Autosize? The Autosize property for those 3 command buttons is set to True. I tried setting them to False, but that didn't change anything. "

AutoSize by default is False. You had to have deliberately changed it. Why? If setting to back to False did not work...hmmmm....I must admit I am a little stuck with that, as it works fine for me.

Is there something in the Style of the paragraph that is weird?

I have been trying and I can not duplicate this problem.



Gerry

 

[hellohello1]

I figured it out!

I had to uncheck 'lock aspect ratio'. I right clicked on my command button and selected 'Format Control'. Then in the Size tab, I had to uncheck 'lock aspect ratio'.

Thanks for writing back and trying to help!

 

[SkipVought]

Ahhhhh...

make buttons...

invible...

with Liberty and Justice for all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}