Reference Cmd Button

[CR]

I have a worksheet, we'll say Sheet 1, that does a web query. I have two
command buttons, from the control toolbox, set up that turn the query
on/off.

On either the workbook Open or Beforeclose, I want to make one of the
buttons visible = false.

When I put the code in ThisWorkbook module, I don't seem to know how to
reference the button (or maybe the sheet?)

I get an object required error as soon as it gets to my Cmdb_Stop.Visible =
False line.

How do I properly point to it?


Thanks

 

[Per Jessen]

Hi

You have to use a worksheet reference:

Worksheets("Sheet1").Cmdb_Stop.Visible=False

Regards,
Per

 

[CR]

Thank you.

Hi

You have to use a worksheet reference:

Worksheets("Sheet1").Cmdb_Stop.Visible=False

Regards,
Per

 

[Dave Peterson]

Option Explicit
Private Sub Workbook_Open()

With Me.Worksheets("Sheet1")
.CommandButton1.Visible = True
.CommandButton2.Visible = False
End With

End Sub

(change the names of the buttons to match yours)

I'd use the workbook_Open event. If you use _beforeclose, then the workbook
will have to be saved--and I bet you won't know if the other changes should be
saved.

ps. have you thought of just enabling/disabling them?

With Me.Worksheets("Sheet1")
.CommandButton1.Enabled = True
.CommandButton2.Enabled = False
End With

 

[CR]

Thank you, for the reply

I would have rather put it in the Open event.
I already have a Sub in the Close to make sure the Query is turned off
before the next time the book opens. I just put it in there, because I
already get the Save? box

The two buttons are on top of each other, one to start the Query and one to
stop. They're visibility turns on and off.

I do not want to run the Query every time, as it overwrites the prievous.

If the user closes while running a Query the button visibility was wrong on
open, hence my post.

If I put everything in the on Open, the "Refresh Query?" dialog box shows up
which I didn't want.

Everything would still work if the user said No to the dialog box; but the
dialog box is distracting, and they might push Yes when they really didn't
want to.