VBA: Restarting a sub when worksheet is activated.

Jeester

New Member
Joined
Sep 6, 2014
Messages
17
I currently have:

Code:
Private Sub CommandButton1_Click()
    CommandButton1.Enabled = True
    
        If Range("$B$7") = "" Then
            CommandButton1.Enabled = False


            Else
                CommandButton1.Enabled = True
        End If
    
    ActiveWorkbook.Sheets(2).Activate


End Sub

Basically, when Cell B7 has nothing in it the Command Button is disabled but then when I type something back into it, it won't enable again.
How can I make it loop round so that it will enable again when I type something into B7.

Cheers for the help,

This forum has been so good I am looking forward to being good enough to help others!

Jeester
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Where is he command button located?

Also, why are you using the Click event of the button itself to set it's enabled property?

If the button is disabled you won't be able to click it.:)
 
Upvote 0
Look into a worksheet change event.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(7, 2) = "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub
 
Upvote 0
Norie said:
Where is he command button located?

Also, why are you using the Click event of the button itself to set it's enabled property?

If the button is disabled you won't be able to click it.
icon_smile.gif
I'm still very much a VBA virgin (three days now!) and now you mention it I want to kick myself. The person below me seems to have done it your way. :)

Thank you for your input!

Also, it's located at the top of the first page.

"If the button is disabled you won't be able to click it."

That is exactly the problem I was having haha, I couldn't figure out how to get it undisabled (not a real word!).
 
Upvote 0
Look into a worksheet change event.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(7, 2) = "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

Thank you for your help,
For some reason your code didn't work in the Worksheet_Change thingy, I had to change it to Worksheet_Activate.

For future reference my code is now:

Code:
Private Sub CommandButton1_Click()
    
    ActiveWorkbook.Sheets(6).Activate


End Sub


Private Sub Worksheet_Activate()


        If Cells(7, 2) = "" Then
            CommandButton1.Enabled = False


            Else
                CommandButton1.Enabled = True
        End If


End Sub

Thank you for your help! You helped show me the light.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top