Run Macro from cmd button error

Blur2037 · Nov 17th 2005

I am having trouble running a macro that I got off this site. It just protect/unprotects all the sheets at once. I want to just have a cmd button that runs it instead of have to go up to the macrolist. People will be using this that have no idea what a macro is. I get an error that reads "Run-time error '1004': Method 'Protect' of object '_Worksheet' failed". If I just run the macro from the list, no error. I have the cmd button call the module and then the module shows the userform. When I debug it takes me to either of the lines within the if else statement, depending on if the sheet is already protected or not. Here is my code, and any help would be much appreciated.

Code

Private Sub cmdPass_Click()
   Call ShowPass
End Sub


Sub ShowPass()
    UserForm1.Show
End Sub


Private Sub cmdOK_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
    Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

Display More

thanks

acw · Nov 17th 2005

Re: Run Macro from cmd button error

HI

Code worked for me. Where do you have each of these pieces of Code?

Tony

**Jack in the UK** · Nov 18th 2005

Re: Run Macro from cmd button error

Be sure UserForm1 exists

jiuk

Blur2037 · Nov 18th 2005

Re: Run Macro from cmd button error

This is the code for Module1

Code

Sub ShowPass()
    UserForm1.Show
End Sub

UserForm1 does exist. This is the code in UserForm1

Code

Private Sub cmdOK_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
    Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

Display More

This is the code for the command button on the the spreadsheet

Code

Private Sub cmdPass_Click()
   Call ShowPass
End Sub

Are there some sort of rules that I could have in place that I dont know about. I just got this computer from somebody else at work recently and havent yet become too familiar with it. I've tried this on my neighbors computer with no such luck. thanks for the help guys

Blur2037 · Nov 19th 2005

Re: Run Macro from cmd button error

i tried this on my computer at home and it does work. it must be something with the settings at my work computer.

**royUK** · Nov 19th 2005

Re: Run Macro from cmd button error

Do you want the UserForm to show at the opening of the workbook? I can't see why you don't just put the code into the CommandButton

Code

Private Sub cmdPass_Click() 
    UserForm1.Show 
End Sub

Then try this alternative

Code

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 19/11/2005 17:59
' Author    : royUK
' Website   : [url]www.excel-it.com[/url]
' Purpose   : Protect/Unprotect sheets
'---------------------------------------------------------------------------------------
Option Explicit


Private Sub cmdOK_Click()
    Dim ws As Worksheet
    Dim strPW As String
    strPW = "test"
    For Each ws In Worksheets
        If Not ws.ProtectContents = True Then
            ws.Protect (strPW)
        Else
            ws.Unprotect (strPW)
        End If
    Next ws
    Unload Me
End Sub

Display More

Blur2037 · Nov 21st 2005

Re: Run Macro from cmd button error

no, I do not want it to open at startup. Lots of people use it for read-only purposes. I tried just putting the code in the command button. no such luck tho. I tried your code royUK. It did the same thing. It has to be something within the setting or the controls of excel with respect to the macros. Its very odd because if I run the macro from the list it works, but not with the command button. What is also odd is that I have other command buttons that run macros and they work fine. I dont know what to do. If I cant get it to work then I cant get it to work. What can ya do.

Insomniac · Nov 21st 2005

Re: Run Macro from cmd button error

Sounds like your using xl97?

See here:http://support.microsoft.com/d…n-us;177527&Product=xlw97

Set the TakeFocusOnClick Property to False of the Command Button

or put in as 1st line of code:
[vba] Activecell.Activate[/vba]

Blur2037 · Nov 22nd 2005

Re: Run Macro from cmd button error

that one line of code did it for me. thanks a lot. this has been drivin' me nuts.

Run Macro from cmd button error

Participate now!

Share