Selection returns “Compile Error: Expected Function or variable”

Question

Help!

Below is my code for a start/stop button in Excel and now I'm getting a Compile error that is highlighting btnStart on the first line.

Sub btnStart()
ActiveSheet.Unprotect
    Cells(Rows.Count, 5).End(xlUp).Offset(1) = Date
    Cells(Rows.Count, 6).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Cells(Rows.Count, 8).End(xlUp).Offset(1) = Environ("username")
    Me.btnStart().Enabled = False
    Me.btnStop.Enabled = True

End Sub

Sub btnStop()
ActiveSheet.Unprotect
    Cells(Rows.Count, 7).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Me.btnStart.Enabled = True
    Me.btnStop.Enabled = False
End Sub

Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Do you actually have buttons named `btnStart` and `btnStop`? — RubberDuck, Jan 05 '15 at 20:23
I've assigned macros to buttons? Please forgive any ignorance — Laura Walker, Jan 05 '15 at 20:28
And are those buttons named exactly as you have them in your code? — RubberDuck, Jan 05 '15 at 20:29
Just checked. Yes it is. Still get errors with both Thank you — Laura Walker, Jan 05 '15 at 20:32
What type of button are they? - Forms or activeX ? And where is your code - all in the worksheet module? — Tim Williams, Jan 05 '15 at 20:43

score 1 · Answer 1 · edited May 23 '17 at 12:05

If I understand correctly the buttons are on an excel sheet (not in a user form) so your issue because you are calling the buttons incorrectly. you can't simply call the named buttons you must look into the sheet.buttons property like this

ActiveSheet.Buttons("btnStart").

Also if you have named sheets I would use the sheetname rather than ActiveSheet

Sheets("sheetname").Buttons("btnStart").

One more note, that enabling/disabling a button works BUT it doesn't make the button appear enabled/disabled. To do this you also have to change the font color.

ActiveSheet.Buttons("btnStart").Font.ColorIndex = 15 '15 is grey, 1 is black

---- edit: code changed ---

REASON: After doing some more research it seems there are problems with my original solution. The most important is that the "enabled" property has no effect in excel 2010. Another route would be using activeX controls, BUT, a recent windows update (dec 2014) prevents activeX controls from running without deleting some system files (which would have to be done for each user, on every computer this code may run on -_- good job MS SOURCE)

This new solution should avoid all those problems. It uses two global variables start_btn_disabled and stop_btn_disabled I assume each button in your form (btnStart and btnStop) have a macro assigned to them? Simply check the global variable at the very beginning of the the sub if the button is disabled then quit the sub, so even though the click is still processed (it will always be processed in excel 2010 as stated before) the code doesn't run. So it behaves as though it was disabled. In my code I made a sub called btnStopClicked that would run when you click 'btnStop' In order to assign the macro to a button, right click the button, select "assign macro" and select the appropriate macro. I also created a similar sub for when you click the start button

'these are global variables and should be declared at the top of the module 
'outside of any sub/function
'
'Note we use DISabled rather than enabled, because by default 
'booleans = False. This means as soon as the form opens both these buttons 
'will be enabled without any extra work. 
'If you want to change this (make start button enabled and stop disabled,
'when the workbook opens simply change all the "stop_btn_disabled" to 
'"stop_btn_enabled" and uncomment the following line) make sure you change the 
'variable names so they make sense
'Dim stop_btn_enabled As Boolean    'initializes to false
Dim start_btn_disabled  As Boolean  'intializes to false
Dim stop_btn_disabled As Boolean    'intializes to false

'Most of this code remains the same as before

Sub btnStart()
    ActiveSheet.Unprotect
    Cells(Rows.Count, 5).End(xlUp).Offset(1) = Date
    Cells(Rows.Count, 6).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Cells(Rows.Count, 8).End(xlUp).Offset(1) = Environ("username")

    'now we set the state of the global variable
    start_btn_disabled = True
    'makes the button appear greyed out
    ActiveSheet.Buttons("btnStart").Font.ColorIndex = 15

    'now we set the state of the global variable
    stop_btn_disabled = False
    'makes the button black like normal
    ActiveSheet.Buttons("btnStop").Font.ColorIndex = 1

End Sub

Sub btnStop()
    ActiveSheet.Unprotect
    Cells(Rows.Count, 7).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"

    'now we set the state of the global variable
    stop_btn_disabled = True
    'makes the button appear greyed out
    ActiveSheet.Buttons("btnStop").Font.ColorIndex = 15

    'now we set the state of the global variable
    start_btn_disabled = False
    'makes the button black like normal
    ActiveSheet.Buttons("btnStart").Font.ColorIndex = 1
End Sub

'and now the real key is checking the globals before running the code

'when you click "btnStop" button this is the code that runs, you may have
'named the sub something different, I just named it this way so it's clear
'what the sub does
Sub StopBtnClicked()
    'must be the first bit of code in the btn click sub
    If (stop_btn_disabled) Then
        Exit Sub
    End If

    'the rest of the code when you click stop button goes here
    'the only way to get to this point is if the but is enabled
End Sub

Sub StartBtnClicked()
    'must be the first bit of code in the btn click sub
    If (start_btn_disabled) Then
        Exit Sub
    End If

    'the rest of the code when you click start
End Sub

if this solves your problem, please mark it as the answer

Thank you so much for this help! the button works but there's this window that appears with a red circle and an X in it and all the window says is 400?? — Laura Walker, Jan 06 '15 at 18:19
I have updated my code complete with explanation, please re-read it. There are some pretty big changes but it should work now — andrew, Jan 06 '15 at 20:18
Andrew thank you so very much for your help. and I'm sorry to have to ask this but is there any way i can see the code all as one? it's a little confusing, for me at least to see where these new parts fit in with the rest of your helpful code. :-( — Laura Walker, Jan 06 '15 at 21:09

score 0 · Answer 2 · answered Jan 05 '15 at 20:20

Maybe try the following, I corrected one line

Sub btnStart()
ActiveSheet.Unprotect
    Cells(Rows.Count, 5).End(xlUp).Offset(1) = Date
    Cells(Rows.Count, 6).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Cells(Rows.Count, 8).End(xlUp).Offset(1) = Environ("username")
    'Corrected line below
    Me.btnStart.Enabled = False
    Me.btnStop.Enabled = True

End Sub

Sub btnStop()
ActiveSheet.Unprotect
    Cells(Rows.Count, 7).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Me.btnStart.Enabled = True
    Me.btnStop.Enabled = False
End Sub

Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

I still get the same error for both buttons. thank you though! — Laura Walker, Jan 05 '15 at 20:22

score 0 · Answer 3 · answered Jan 05 '15 at 20:49

0

You need to use ActiveX buttons if you want to use the Enabled property:

Private Sub btnStart_Click()

    Me.btnStart.Enabled = False
    Me.btnStop.Enabled = True

End Sub

Private Sub btnStop_Click()

    Me.btnStart.Enabled = True
    Me.btnStop.Enabled = False

End Sub

answered Jan 05 '15 at 20:49

Tim Williams

122,926
8
79
101

Thank you tim! I'll post revised code to see if i have it correct?? – Laura Walker Jan 05 '15 at 20:54
It's not clear what the "Stop" code is doing - do you intend it to replace the value already in Col G ? It will insterad put the "stop" time in the next row (since you're using offset())... – Tim Williams Jan 05 '15 at 20:59

Selection returns “Compile Error: Expected Function or variable”

3 Answers3