Setting a public variable

[Patrick C. Simonds]

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]

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)

 

[Dave Peterson]

Oops. Typing mistake...

if me.optionbutton1.value = true then
mylocked = true
elseif me.optionbutton2.value = true then
mylocked = false
end if

 

[Patrick C. Simonds]

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]

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]

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]

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).

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

 

[Patrick C. Simonds]

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]

That would cause a problem.

Glad you found it.

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.