Referencing a Forms Button in a Worksheet

[Ryan H]

I have little experience with Forms controls on worksheets. I have a piece
of code that should disable or enable a Forms command button on
Sheets("QUOTE") depending on a value in a cell (True or False). The command
buttons name is Edit_Add-In. I am getting an error saying "Unable to get the
Buttons property of the Worksheets class". Any ideas? Thanks in Advance!

' fill array with all users information
aryUserInfo = Range(Cells(1, "B"), Cells(1, "G")).Value

' enable/disable Edit Add-In button
Error>>Sheets("QUOTE").Buttons("Edit_Add-In").Enable = CBool(aryUserInfo(1,
6))

 

[Dave Peterson]

First, it's .enabled (with a D)

Second, change the name of the button by replacing the dash with an underscore.
(don't forget to fix the code, too.)

Then try it.

 

[Ryan H]

Oops, sorry for the misspelling. I didn't cut and paste my code like I
should have. I changed the name of the button to cmbEditAddIn and this code
works great! Why didn't it throw an error with the "-" in the name?

' enable/disable Edit Add-In button
With .Buttons("cmbEditAddIn")

.Enabled = CBool(aryUserInfo(1, 6))

If CBool(aryUserInfo(1, 6)) Then
.Font.ColorIndex = xlAutomatic 'black
Else
.Font.ColorIndex = 48 'grey
End If
End With
--
Cheers,
Ryan

First, it's .enabled (with a D)

Second, change the name of the button by replacing the dash with an underscore.
(don't forget to fix the code, too.)

Then try it.

 

[Dave Peterson]

Did you mean "why did it throw an error with the "-" in the name?"

I'd guess that names that contain the hyphens are illegal. (Not really going
out on a limb for that, huh?)

Oops, sorry for the misspelling. I didn't cut and paste my code like I
should have. I changed the name of the button to cmbEditAddIn and this code
works great! Why didn't it throw an error with the "-" in the name?

' enable/disable Edit Add-In button
With .Buttons("cmbEditAddIn")

.Enabled = CBool(aryUserInfo(1, 6))

If CBool(aryUserInfo(1, 6)) Then
.Font.ColorIndex = xlAutomatic 'black
Else
.Font.ColorIndex = 48 'grey
End If
End With

 

[Ryan H]

Thanks for the reply Dave. I realize "-" are not allowed, but it did NOT
throw an error when I assigned a name to the forms button on the worksheet,
weird huh? This what I did.

I drew the forms button on the worksheet, didn't assign a macro, but made
sure the button was still selected. Then I put Selection.Name = "Edit
Add-In" in the Immediate Window and bang, it worked! Why? I think I missing
something here.
--
Cheers,
Ryan

Did you mean "why did it throw an error with the "-" in the name?"

I'd guess that names that contain the hyphens are illegal. (Not really going
out on a limb for that, huh?)

 

[Dave Peterson]

I don't have an answer for that. To me, it appears to be a bug.

 

[Ryan H]

When you find something like this, should I notify Microsoft or let it be?

 

[Dave Peterson]

It's up to you. I'm sure there's a link somewhere on their site to report
problems. Somewhere.