Referencing a Forms Button in a Worksheet

  • Thread starter Thread starter Ryan H
  • Start date Start date
R

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))
 
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.
 
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


Dave Peterson said:
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.
Click to expand...
 
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?)

Ryan said:
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
Click to expand...
 
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


Dave Peterson said:
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?)
Click to expand...
 
It's up to you. I'm sure there's a link somewhere on their site to report
problems. Somewhere.
 
Back
Top