CODEWITHSUNDEEP
vbaexcel
Steve
Steve

Reputation: 67

Change macro form button action from workbook to active worksheet

Hi I have been using the following code in a macro assigned to a form button that is on several sheets in the same workbook

Sub RunAll()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select
Application.ScreenUpdating = False
Call UnprotectAll
Call BasicLoop
Call ProtectAll
Next wks
Application.ScreenUpdating = True
End Sub

here are the macros it calls

Sub UnprotectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = ""

For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect Password:=yourPassword
Next sh

End Sub


Sub BasicLoop()
For x = 5 To 32
If Cells(x, 9) <> red Then
Cells(x, 10) = "Basic"
Else: Cells(x, 10) = ""
End If

Next x
End Sub

Sub ProtectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = ""

For Each sh In ActiveWorkbook.Worksheets
    sh.Protect Password:=yourPassword
Next sh

End Sub

What I want to change is that only the active sheet is updated on click of the form button and not all the sheets in the workbook? I am still learning so a simple solution would be best cheers!

Upvotes: 0

Views: 453

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Consider:

Sub RunAll()
    Application.ScreenUpdating = False
    Call UnprotectAll
    Call BasicLoop
    Call ProtectAll
    Application.ScreenUpdating = True
End Sub


Sub UnprotectAll()
    Dim sh As Worksheet
    Dim yourPassword As String
    yourPassword = ""
    Set sh = ActiveSheet
    sh.Unprotect Password:=yourPassword
End Sub


Sub BasicLoop()
    For x = 5 To 32
    If Cells(x, 9) <> red Then
        Cells(x, 10) = "Basic"
    Else
        Cells(x, 10) = ""
    End If
Next x
End Sub

Sub ProtectAll()
    Dim sh As Worksheet
    Dim yourPassword As String
    yourPassword = ""
    Set sh = ActiveSheet
    sh.Protect Password:=yourPassword
End Sub

Upvotes: 0

Netloh
Netloh

Reputation: 4378

All you need to do is to remove the loops that runs through all of the worksheets. You code should then look like this:

Sub RunAll()
    Application.ScreenUpdating = False
    Call UnprotectAll
    Call BasicLoop
    Call ProtectAll
    Application.ScreenUpdating = True
End Sub


Sub UnprotectAll()
    Dim yourPassword As String
    yourPassword = ""

    ActiveSheet.Unprotect Password:=yourPassword
End Sub


Sub BasicLoop()
    For x = 5 To 32
        If Cells(x, 9) <> red Then
            Cells(x, 10) = "Basic"
        Else
            Cells(x, 10) = ""
        End If
    Next x
End Sub

Sub ProtectAll()
    Dim yourPassword As String
    yourPassword = ""

    ActiveSheet.Protect Password:=yourPassword
End Sub

This will only work execute on the active sheet.

Upvotes: 0

Related Questions