Conditionally executing a macro in Excel

[Jean01]

Hello,
In an Excel 97 worksheet, I have created a checkbox that executes a macro and adds a custom toolbar when checked and another macro to remove the custom toolbar when unchecked. I have assigned a cell in order to store the value of the checkbox so that the worksheet "remembers" the state of the checkbox when the worksheet is re-opened.
What I would like it to do is that the macros would execute depending on the actual value stored in the cell rather that executing whenever the state of the checkbox just changes.
This would allow the many users using the spreadsheet to choose if they want the custom toolbar installed whenever they open the workbook or not.

Thanks in advance

 

[kevinclark]

Could you use the Autpen() macro to perform a check on the condition of the assigned cell and present the toolbar accordingly. The Autpen() macro runs automatically when ever a workbook first opens.

Example:

Sub Autpen()
If Sheets("Sheet1&quot.Range("A1&quot.Value = "SHOW" Then
'Run code to show the ToolBar
Else
'Run code to hide the ToolBar
End If
End Sub

 

[Jean01]

Kevin,
I assume that his Auto-Open macro should be stored in "ThisWorkbook" object right?
I looked in the help files and could'nt find anything that talks about the Autpen Macro.
In the code you sugested, is ("Sheet1&quot the name of the sheet that is displayed on the Tab or the name of the Microsoft Excel Object?
Is "SHOW" an actual value stored in cell A1 or should I replace SHOW with what ever the value stored in cell A1?


Thanks in advance

 

[Jean01]

Kevin,
I assume that this Auto-Open macro should be stored in "ThisWorkbook" object right?
I looked in the help files and could'nt find anything that talks about the Autpen Macro.
In the code you sugested, is ("Sheet1&quot the name of the sheet that is displayed on the Tab or the name of the Microsoft Excel Object?
Is "SHOW" an actual value stored in cell A1 or should I replace SHOW with what ever the value stored in cell A1?


Thanks in advance

 

[kevinclark]

Hi Jean
In my previous example I mentioned the Autpen() routine which is a left over from older versions of MS Office, and probably no longer documented. Newer versions of MS Office now use a Workbook_Open() routine. While either will work it is probably wiser to use Workbook_Open().

1) Open your Workbook
2) Choose the Visual Basic Editor From your Tools .... Macro menu
3) Double click on this Workbook under the project window
4) In the 'This Workbook' module select 'Workbook' from the drop down menu
5) This inserts the Workbook_Open() routine for you. Each time the Workbook opens it will read this routine and run anything contained in it.

Now I am assuming that you already have two routines, 1 for opening the Toolbar and 1 for hiding it. Therefore all you need to do is write a conditional 'If' statement inside the Workbook_Open routine.

Base the 'If' Statement on the Cell that contains the value that you write too, the one you assigned to the checkbox. For example lets say if the user checks the checkbox the value of that cell is 1, and if they uncheck it the value is 0.

Your if statement would be something like the following

If Sheets(WS Name).Range(Range).value = 1 then
call your routine to open the Toolbar
Else
call your routine to hide the Toolbar
End If