Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
On Userform1 I have 2 OptionButtons.
I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
In a General module (not behind a worksheet and not under the userform):
Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's ..Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
Oops. Typing mistake...
if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = false end if Dave Peterson wrote: In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
So I Created a module (module1) and selected General : Declaration and
inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
I'm not sure what that means.
Are you saying that if you set a break point on the "on error" line and then show the form and click on commandbutton1, the procedure doesn't run? "Patrick C. Simonds" wrote: So I Created a module (module1) and selected General : Declaration and inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
Thanks for your patients, let me try to explain.
When UserForm1 displays the user is presented with 2 optionsbuttons. Option 1 open the file for viewing only (Optionbutton 1 is set to true by default), Option 2 to edit the file. If they select Option 1, the workbook is opened and protected to the point that the user can not edit the workbook in any way. If the chose Option 2 (to edit the workbook) they must provide a password to continue. All of that works well. My issue is, that if the user selects Option 1 (to view only) I do not want them to be able to save/save as the workbook. So once they click on Commandbutton1 to accept Option1 the Userform is closed and the user then browses the workbook viewing the information within. I then created the routine below to prevent the save As function from working but it apparently does not see that myLocked = True. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) " If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... I'm not sure what that means. Are you saying that if you set a break point on the "on error" line and then show the form and click on commandbutton1, the procedure doesn't run? "Patrick C. Simonds" wrote: So I Created a module (module1) and selected General : Declaration and inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
Are you finding the problem when you're testing?
If you are, then I bet that you're resetting that myLocked variable inadvertently. It could be that you're hitting the Reset button in the VBE. (Or you could have an "End" (not "End if", "End Select", "End Sub") line in your code.) Either of these will reset all these public variables (to 0's for numbers, empty strings for text, nulls for variants, and false for booleans). "Patrick C. Simonds" wrote: Thanks for your patients, let me try to explain. When UserForm1 displays the user is presented with 2 optionsbuttons. Option 1 open the file for viewing only (Optionbutton 1 is set to true by default), Option 2 to edit the file. If they select Option 1, the workbook is opened and protected to the point that the user can not edit the workbook in any way. If the chose Option 2 (to edit the workbook) they must provide a password to continue. All of that works well. My issue is, that if the user selects Option 1 (to view only) I do not want them to be able to save/save as the workbook. So once they click on Commandbutton1 to accept Option1 the Userform is closed and the user then browses the workbook viewing the information within. I then created the routine below to prevent the save As function from working but it apparently does not see that myLocked = True. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) " If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... I'm not sure what that means. Are you saying that if you set a break point on the "on error" line and then show the form and click on commandbutton1, the procedure doesn't run? "Patrick C. Simonds" wrote: So I Created a module (module1) and selected General : Declaration and inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
Solved my problem.
Originally I placed "Public myLocked As Boolean" under ThisWorkbook and it did not work. So I created a new Module and placed it there, but, I did not remove it from ThisWookbook. As soon as I removed it from there, everything works. "Dave Peterson" wrote in message ... Are you finding the problem when you're testing? If you are, then I bet that you're resetting that myLocked variable inadvertently. It could be that you're hitting the Reset button in the VBE. (Or you could have an "End" (not "End if", "End Select", "End Sub") line in your code.) Either of these will reset all these public variables (to 0's for numbers, empty strings for text, nulls for variants, and false for booleans). "Patrick C. Simonds" wrote: Thanks for your patients, let me try to explain. When UserForm1 displays the user is presented with 2 optionsbuttons. Option 1 open the file for viewing only (Optionbutton 1 is set to true by default), Option 2 to edit the file. If they select Option 1, the workbook is opened and protected to the point that the user can not edit the workbook in any way. If the chose Option 2 (to edit the workbook) they must provide a password to continue. All of that works well. My issue is, that if the user selects Option 1 (to view only) I do not want them to be able to save/save as the workbook. So once they click on Commandbutton1 to accept Option1 the Userform is closed and the user then browses the workbook viewing the information within. I then created the routine below to prevent the save As function from working but it apparently does not see that myLocked = True. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) " If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... I'm not sure what that means. Are you saying that if you set a break point on the "on error" line and then show the form and click on commandbutton1, the procedure doesn't run? "Patrick C. Simonds" wrote: So I Created a module (module1) and selected General : Declaration and inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a public variable
That would cause a problem.
Glad you found it. "Patrick C. Simonds" wrote: Solved my problem. Originally I placed "Public myLocked As Boolean" under ThisWorkbook and it did not work. So I created a new Module and placed it there, but, I did not remove it from ThisWookbook. As soon as I removed it from there, everything works. "Dave Peterson" wrote in message ... Are you finding the problem when you're testing? If you are, then I bet that you're resetting that myLocked variable inadvertently. It could be that you're hitting the Reset button in the VBE. (Or you could have an "End" (not "End if", "End Select", "End Sub") line in your code.) Either of these will reset all these public variables (to 0's for numbers, empty strings for text, nulls for variants, and false for booleans). "Patrick C. Simonds" wrote: Thanks for your patients, let me try to explain. When UserForm1 displays the user is presented with 2 optionsbuttons. Option 1 open the file for viewing only (Optionbutton 1 is set to true by default), Option 2 to edit the file. If they select Option 1, the workbook is opened and protected to the point that the user can not edit the workbook in any way. If the chose Option 2 (to edit the workbook) they must provide a password to continue. All of that works well. My issue is, that if the user selects Option 1 (to view only) I do not want them to be able to save/save as the workbook. So once they click on Commandbutton1 to accept Option1 the Userform is closed and the user then browses the workbook viewing the information within. I then created the routine below to prevent the save As function from working but it apparently does not see that myLocked = True. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) " If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... I'm not sure what that means. Are you saying that if you set a break point on the "on error" line and then show the form and click on commandbutton1, the procedure doesn't run? "Patrick C. Simonds" wrote: So I Created a module (module1) and selected General : Declaration and inserted: Public myLocked As Boolean Option Explicit Then placed your suggested code in the CommandButton1_Click() event Private Sub CommandButton1_Click() On Error GoTo Termination If Me.OptionButton1.Value = True Then myLocked = True Unload UserFormAccess Call Protection.protect_all_sheets Else myLocked = False Unload UserFormAccess UserFormPassword.Show End If GoTo LastLine Termination: LastLine: End Sub Then added the If myLocked = True line in the routine below, but the If statement does not run. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If myLocked = True Then If SaveAsUI = True Then Cancel = True End If End Sub "Dave Peterson" wrote in message ... In a General module (not behind a worksheet and not under the userform): Public myLocked as boolean Then you can just use that variable in any procedure--like the Ok button or the Cancel button or wherever you want. I wouldn't use Locked as a variable name. It looks way too close to a Range's .Locked Property name. And it may not confuse VBA, but it could confuse me. if me.optionbutton1.value = true then mylocked = true elseif me.optionbutton2.value = true then mylocked = true end if Or you may be able to just look at the first and know (only two options and one must be selected???): if me.optionbutton1.value = true then mylocked = true else mylocked = false end if or its equivalent: mylocked = cbool(me.optionbutton1.value = true) "Patrick C. Simonds" wrote: On Userform1 I have 2 OptionButtons. I need some way to set a public variable (called Locked) to true if OptionButton1 is true when Userform1 closes or to false if OptionButton2 is true -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public variable (or Public Const?) | Excel Programming | |||
Public variable (or Public Const?) | Excel Programming | |||
Public variable | New Users to Excel | |||
Setting Public Variables Error | Excel Programming | |||
Setting value of a public var from a form | Excel Programming |