Macros Issue

[Sara]

I have made a questionnaire in excel which has drowdown lists, hidden rows
that pop up if you check certain cells, font and background change colors
according to the option button selection etc. This form is to be emailed to
our clients, and when i email it to others the security settings disable the
macros automatically on some computers. I have to find a way around this
because we can't tell each of our clients to change their settings. I am
thinking maybe protection the wooksheet may help?

I tried to protect part of the worksheet to see if I can still run the
macros, but it asks me for a password every time I click on the option button
(see code below). I don't want that since I will ultimately be hiding the
password and the code from the client. I just want them to be able to run the
macros and be able to type in the unlocked cells.

I made this form looking at onther one which is just like mine. It is
protected and emailing it doesnt cause the macro security issue. I am just
not sure how they did it since I don't have the password to unprotect their
sheet. Please advise...

Thanks

Private Sub OptionButton1_Change()

Worksheets("Job Info Sheet").Unprotect 'Password:="xyz"

If OptionButton1.Value = True Then
Range("A2934").Font.ColorIndex = 0
Range("E29:K34").Interior.ColorIndex = 0
Else
Range("A3333").Font.ColorIndex = 36
Range("E33:K33").Interior.ColorIndex = 36
End If

Worksheets("Job Info Sheet").Protect 'Password:="xyz"

End Sub

 

[CurlyDave]

I have made a questionnaire in excel which has drowdown lists, hidden rows
that pop up if you check certain cells, font and background change colors
according to the option button selection etc. This form is to be emailed to
our clients, and when i email it to others the security settings disable the
macros automatically on some computers. I have to find a way around this
because we can't tell each of our clients to change their settings. I am
thinking maybe protection the wooksheet may help?

I tried to protect part of the worksheet to see if I can still run the
macros, but it asks me for a password every time I click on the option button
(see code below). I don't want that since I will ultimately be hiding the
password and the code from the client. I just want them to be able to runthe
macros and be able to type in the unlocked cells.

I made this form looking at onther one which is just like mine. It is
protected and emailing it doesnt cause the macro security issue. I am just
not sure how they did it since I don't have the password to unprotect their
sheet. Please advise...

Thanks

Private Sub OptionButton1_Change()

Worksheets("Job Info Sheet").Unprotect 'Password:="xyz"

If OptionButton1.Value = True Then
Range("A2934").Font.ColorIndex = 0
Range("E29:K34").Interior.ColorIndex = 0
Else
Range("A3333").Font.ColorIndex = 36
Range("E33:K33").Interior.ColorIndex = 36
End If

Worksheets("Job Info Sheet").Protect 'Password:="xyz"

End Sub

You have the password commented in your code

Worksheets("Job Info Sheet").Unprotect Password:="xyz"
'your code
Worksheets("Job Info Sheet").Protect Password:="xyz"

The users will have to enable macros for macros to work in a Workbook.
You could use a macro to hide all but one sheet when you close the
book

Have a macro unhide the sheets when the workbook is opened . If macros
are not "enabled" the sheets will not show, and the user cannot use
the workbook.

The sheet that is not hidden should have some information for the
user, telling them that macros have to be enabled to use the workbook,
and give some instruction on how to set the security level as well.

 

[Sara]

I am not sure how to get all that done because I am fairly new to VBA. I just
don't understand how the other excel file is working. Is there any way I can
show these files to someone? Maybe looking that them will help.