help: master control macro and user form

  • Thread starter Thread starter jasonsweeney
  • Start date Start date
J

jasonsweeney

This is what I want to do (and I am new at this):

I want to create a user form that has a check box for "Enable all
macros" that if checked, allows all the macros in my spreadsheet to
operate. But if I uncheck it then none of them work.

Thus, I envison that the opening line of each sub-routine would begin
with a line that <If [useform check box = yes" then continue on with
code, otherwise, exit sub]>


I have two questions:

(1) Any help on how to the code beggining of each sub would be
appreciated/

(2) In the past, I have a userform collect a bunch of information from
the spreadsheet administrator....but all that input is dumped into
cells in the spreadhseet, as opposed to want I want to do, which is to
store the information IN the userform....

FOr example, I may have a place in the userform the administrator to
enter the name of one other person that can access the adiminstrative
userform. In order to "save" that person's name, I have the text-box
enter that name into a cell in the work sheet. Then in the furture,
the "authorized person" is linked to the cell which the user can
change.

But note that I always have the name in a CELL in the spreadsheet. Is
there another way to preserve this information? Can the userform text
boxes be used to store text like this?

All help is appreciated.
 
Jason,

I wouldn't use the userform to store the data, it might get closed down. I
suggest using a workbook name to store it. A smart user could go in and
change it, but there are always around any lockout code.

This code would create the name initially, probably best to put in
Workbook_Open

If IsError(Evaluate("run.macros")) Then _
ThisWorkbook.Names.Add Name:="run.macros", RefersTo:=False

And in the userform checkbox click event add this code

Private Sub CheckBox1_Click()

ThisWorkbook.Names.Add Name:="run.macros", RefersTo:=CheckBox1.Value

End Sub

In the macros, precede the code with this sort of test

If Not Evaluate(Names("run.macros").RefersTo) Then
Exit Sub
Else
...
End If

You could do something similar with other information.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top