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:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,804
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.
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,490
Messages
5,523,249
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top