• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform to find data on all worksheets in workbook

@Somendra Misra & @Luke M

Would the below code work in the workbook_open event:

Code: 
Private Sub Workbook_Open()

'If you have different passwords

'for each Worksheet.

Sheets(1).Protect Password:="Secret", _
UserInterFaceOnly:=True

Sheets(2).Protect Password:="Carrot", _
UserInterFaceOnly:=True

'Repeat as needed.

End Sub
 
Let's pause a bit before editing the code.
I am trying to add information to the tab that has been protected. In essence, I want to be able to add data to the tab but other users cannot delete or amend this data without a password.
Click to expand...
From this statement, it sounds like you are doing this regularly...but I think it's via another UserForm?
 
If it's via code, then you have two options:
Option 1) SM's advice is to setup the protection with UserInterfaceOnly:=True. What this does is tell XL to only protect the sheet from the user, but let macros make changes normally. This provide ease in the coding, but the caveat is that you have to set this parameter on opening the workbooking, it doesn't get preserved when you save, close, and re-open. :(

Option 2) In the code that needs to add to the workbook, have it unprotect the sheet first. Something like:
Code: 
Me.Unprotect
Me.Controls("Shipment" & X).Value = ""
Me.Protect
 
@Luke M

Hi,

Can you explain this part of your statement:

".... but the caveat is that you have to set this parameter on opening the workbooking, it doesn't get preserved when you save, close, and re-open."

Regards,
 
For the UserInterfaceOnly (UIO) to work, the protection has to be set in the current viewing session. If you protect the sheet, save and close, and then reopen the workbook, the UIO won't be enabled, and the macro won't be able to do it's job.
You can work around this by re-protecting the sheet on Workbook_Open, but I find that annoying. (Note that you don't have to Unprotect, VBA lets you re-apply a Protect even on a sheet that's already protected)

To illustrate:
Sample file has a macro which changes value in cell A1. Run the MacroProtect code, and you can use the button. Save, close, re-open, and button doesn't work.
 

Attachments

  • ExampleProtect.xlsm
    17.8 KB · Views: 29
sealion1 said:
@Somendra Misra & @Luke M

Would the below code work in the workbook_open event:

Code: 
Private Sub Workbook_Open()

'If you have different passwords

'for each Worksheet.

Sheets(1).Protect Password:="Secret", _
UserInterFaceOnly:=True

Sheets(2).Protect Password:="Carrot", _
UserInterFaceOnly:=True

'Repeat as needed.

End Sub
Click to expand...
Yeah, that would do it.
 
Luke M said:
For the UserInterfaceOnly (UIO) to work, the protection has to be set in the current viewing session. If you protect the sheet, save and close, and then reopen the workbook, the UIO won't be enabled, and the macro won't be able to do it's job.
You can work around this by re-protecting the sheet on Workbook_Open, but I find that annoying. (Note that you don't have to Unprotect, VBA lets you re-apply a Protect even on a sheet that's already protected)

To illustrate:
Sample file has a macro which changes value in cell A1. Run the MacroProtect code, and you can use the button. Save, close, re-open, and button doesn't work.
Click to expand...


I had used the piece very recently in one of my work file (WorkBook Open), where in I am populating a large amount of data on 4 sheets in the same workbook, which are protected. And it behaves normally even if I save close or re-open the file. It behaves just like it.

Regards,
 
@Somendra Misra & @Luke M

I have tried to put in the below code, but it won't let me put my sheet names in the brackets where there is currently a number - it shows up as an error in red writing. Am I doing something wrong?

Code: 
Private Sub Workbook_Open()

'If you have different passwords
'for each Worksheet.
Sheets(1).Protect Password:="Secret", _
UserInterFaceOnly:=True

Sheets(2).Protect Password:="Carrot", _
UserInterFaceOnly:=True

'Repeat as needed.
End Sub
 
The number is currently just the index. Do it like this, with quotation marks:
Code: 
Worksheets("Sheet1").Protect Password:="Secret", UserInterFaceOnly:=True
 
@Luke M

I am trying this method, but keep getting the debug tool up saying:

upload_2015-1-9_22-47-19.png

The debug tool thinks it is the issue below highlighted in yellow:

upload_2015-1-9_22-49-22.png

Also am I right in thinking that I input the code into the "this workbook" module under:

upload_2015-1-9_22-48-18.png
 
@sealion1

OK do this thing. Un-protect all the sheets with review tab by manually supplying the password. Go to code give password of your choice & save the file. Try opening the file now.

Regards,
 
Yes, code is in right spot. And as SM alludes to, it sounds like the sheet is currently protected with a password, but that password is not "Carrot". You will need to unprotect it first with the current password, and then either let the code protect it or do it manually with the new password, "Carrot".
 
Luke M said:
Cool find! Grabbing the file from the source you found
http://www.excelcampus.com/tools/find-all-vba-form-for-excel/

I was able to change the sheet names just fine in the attached. Note that there is code for the userform, and in a regular module. The UserForm uses Chip Pearson's FindAll functions in the regular module.
Click to expand...


Hello Luke M and sealion1

I have been following this thread with much interest.
I have 2 questions:
1) Is it possible to search based on a list of information?
2) Is it possible to populate the result in a sheet instead of a form?

Thanks for the sterling information.
Regards
AO
 
You must log in or register to reply here.
Back
Top