Macro Help

[Ron99]

Hi Guys...

I have recorded a macro, when I run the macro it does all the action correctly...it basically does some filtering, copy paste to another tab and stuff like that, my problem is it should run in the background, I dont want it to minimize and run the code,

for example I have a button sheet, when I click on that button, i should get a message saying completed, I can add msgbox to the code, not a problem, right now when I click the button, it shows its going to the next tab copying and pasting and everything, i want this to be done at the background,

Is there a code for that ?


second - I want to protect my macro code, I dont want others to view it, is it possible ?

I am using excel 2007

Thank you

 

[Trevor G]

You need to switch the updating off

application.screenupdating=false

at the beginning of your code then

application.screenupdating=true

at the end of your code.

You can set a password to the VBA project under the tools menu.

 

[Ron99]

Thanks Trevor....it works

 

[Ron99]

Hi,

One last question...

I have say 3 tabs

First tab name - Ron
Second tab name- Sam
third tab name - Shawn

when anyone clicks on any of these tab...they should get a pop up to enter a password, only after entering the correct password they can edit the sheet.

I need a code for that...is it possible ?

Thank you

 

[Trevor G]

Select your sheet and then use right mouse button on the sheet tab, then select View Code, change top drop downs to Worksheet and Activate, then use this code, change password to suit.

You would need to do something if they select new sheet.

Private Sub Worksheet_Activate()
Dim strPassword As String
strPassword = "[COLOR=red]Text[/COLOR]"
If InputBox("Please enter the password") <> strPassword Then
MsgBox "Password invalid, please contact the administrator"
Exit Sub
Else
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

 

[Ron99]

Hi,

It works fine so far, but I have one problem...

after the password pop up comes up u can enter the password and it works,
Suppose when I close it without entering the password, it says contact your administrator, which is fine..then I click Ok, after that I can still edit the cells in that sheet which the code shouldnt allow me to do it coz I havent entered the password.

What I prefer here is, after the pop up comes as contact your administrator then I click ok, I need to get the passoword pop up come up again...until they enter the passoword they shouldn't be able to edit the sheet. is that ok


Thanks

 

[Peter_SSs]

Note also that Worksheet_Activate is not triggered on the active sheet when the workbook is opened, so you probably need to consider a way to deal with that.

 

[Trevor G]

Some good advice from Peter (as always). You will need to think how you want things to happen when the workbook is opened.

Behind the sheet then you will have to do something like set the protection on once activated, then use a message box, then input box then response if incorrect and finally unproctect sheet.

So your code would be something like this.

Dim strPassword As String
strPassword = "Text"
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
If InputBox("Please enter the password") <> strPassword Then
MsgBox "Password invalid, please contact the administrator"
Exit Sub
Else
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If