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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

Jeester

New Member
Joined
Sep 6, 2014
Messages
17
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!).
 

Jeester

New Member
Joined
Sep 6, 2014
Messages
17
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,200
Members
430,196
Latest member
rez5656

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
Top