Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cmd Button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cmd Button
Hi
You have to use a worksheet reference: Worksheets("Sheet1").Cmdb_Stop.Visible=False Regards, Per On 4 Nov., 14:42, "CR" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cmd Button
Thank you.
"Per Jessen" wrote in message ... Hi You have to use a worksheet reference: Worksheets("Sheet1").Cmdb_Stop.Visible=False Regards, Per On 4 Nov., 14:42, "CR" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cmd Button
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cmd Button
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. "Dave Peterson" wrote in message ... 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Reference Button | Excel Discussion (Misc queries) | |||
I don't have the relative reference button . why? | Excel Discussion (Misc queries) | |||
Can I get Button name or Cell Reference | Excel Discussion (Misc queries) | |||
Button Name or Cell Reference | Excel Programming | |||
RELATIVE REFERENCE BUTTON | Excel Discussion (Misc queries) |