Referring to a Button on a Form

  • Thread starter Thread starter LarryP
  • Start date Start date
L

LarryP

Help a poor Access guy who is lost in the Excel object model wilderness!

MyWorkbook has worksheet mySheet, which has an ??associated?? form, myForm,
which has a command button, myButton. So it seems to me something like
myWorkbook.mySheet.myForm.myButton ought to point to the button. (As
indicated by the double ??, it's not clear to me that the form is necessary a
child of the worksheet, but I've tried all sorts of different reference
chains and they all generate errors.)

How on earth do I refer to that suckah in VBA so I can manipulate its
colors, visibility, or whatever?
 
I clicked "Yes, it was helpful," but after visiting the link, not so much.
Good general info for beginners, but not enough to solve my specific problem.
Still need to know where a Form, and specifically a Button on a Form,
resides within the Excel Object hierarchy. I can set an Object variable to
ActiveWorkbook, or ActiveWorkbook.ActiveSheet, but if I try
ActiveWorkbook.ActiveSheet.Anythingtodowithmyform I get an error, and since
the Button resides on the Form, I'm nowhere near a valid reference to the
Button.
 
Try something like this:
Right ‘Sheet1’ and click View Code and paste this in:
Private Sub CommandButton1_Click()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show
End Sub

Add a UserForm and add a ComboBox; click RowSource and add this:
=Sheet1!Z2:Z5
Put your fruit names in this Array

Add a CommandButton, double-click the Button ad add this code:
Private Sub CommandButton1_Click()
On Error Resume Next
Sheets("Sheet1").Activate

ActiveCell = ComboBox1.Text
ActiveCell.Offset(1, 0).Select

Unload UserForm1

On Error Resume Next
ActiveWindow.WindowState = xlMaximized
End Sub

I don't know what you're working with there, and my example here is pretty
general, but hopefully you can adapt it to your specific situation.

Regards,
Ryan---
 
Thanks for your interest and helpfulness.

While waiting and pondering I finally found the solution in the Object
Browser, to wit:

VBAProject.MainForm.cmdMyButton

Setting an object variable to that then lets me manipulate the colors and
other attributes of the button. My problem was assuming it had to fall
somewhere under workbook/worksheet.

Havva Goodun!
 
Awesome! Glad you go it working. Wish I could have helped you more, but I
you learned something new all on your own, and that should yield a much
greater sense of satisfaction.
Ryan---
 
Back
Top