Compile Error on button click in VBA form

Question

I get

Compile Error: Expected function or variable

when I select a radio button and click ok on my form. The top line of code where the macro title is gets highlighted in yellow.

My form is four radio buttons and an ok button.

I've checked the macro names and option button names

Private Sub CommandButton1_Click()

    If Ties.Radio_OpPlan.Value = True Then
        Run clearties
        
    ElseIf Ties.Radio_Prior.Value = True Then
        Run TieToPrevious
        
    ElseIf Ties.Radio_Custom.Value = True Then
        Run CustomTie
        
    ElseIf Ties.Radio_NetCase.Value = True Then
        Run NetCaseTie
        
    Else
        output = MsgBox("You need to pick a case to tie to", vbExclamation)
        
    End If
    
    Me.Hide
    output = MsgBox("Case tied out", vbOKOnly)

End Sub

What is Ties? You need to probably declare it – AAA Jun 20 '19 at 22:06 — AAA, Jun 20 '19 at 22:06
it is the name of the form – Erin Williams Jun 20 '19 at 22:11 — Erin Williams, Jun 20 '19 at 22:11

Robert Todar · Answer 1 · 2020-11-11T21:07:47.457

5

Your issue is more than like due to the use of Run. This is used to call a macro from its name as a String.

Try removing Run from your different methods and see if that fixes it.

Private Sub CommandButton1_Click()
    If Ties.Radio_OpPlan.Value = True Then
        clearties
    ElseIf Ties.Radio_Prior.Value = True Then
        TieToPrevious
    ElseIf Ties.Radio_Custom.Value = True Then
        CustomTie
    ElseIf Ties.Radio_NetCase.Value = True Then
        NetCaseTie
    Else
        output = MsgBox("You need to pick a case to tie to", vbExclamation)
    End If

    Me.Hide
    output = MsgBox("Case tied out", vbOKOnly)
End Sub

Additional Notes

Try not to use underscore case. Underscores have special meanings in VBA for events and implementations. So instead of Radio_OpPlan you could do RadioOpPlan, or even better simply OpPlan (really even make OpPlan even more descriptive).
In my opinion, a Case statement looks cleaner in this situation.

Select Case True
    Case RadioOpPlan
        clearties
    Case RadioPrior
        TieToPrevious
    Case RadioCustom
        CustomTie
    Case RadioNetCase
        NetCaseTie
    Case Else
        output = MsgBox("You need to pick a case to tie to", vbExclamation)
End Select

edited Nov 11 '20 at 21:07

answered Jun 20 '19 at 22:35

Robert Todar

2,085
2
11
31

1

`If Ties.Radio_OpPlan.Value = True Then` is simply `If Ties.Radio_OpPlan.Value Then`; similar for the other statements. – AJD Jun 20 '19 at 23:45
@ajd I'm not sure what you are trying to comment on? =) – Robert Todar Jun 21 '19 at 00:23
1

Robert: the coding anti-pattern `If x=True Then`. A simple `If x Then` suffices. Might seem trivial, but that line of thought leads to other anti-patterns that produce 5 lines of code when a simple assignment suffices. – AJD Jun 21 '19 at 00:26
1

@ajd hence why I put in my additional notes an example of how I would suggest going about it (to a certain extent). The first example is simply to show how to remove the error. – Robert Todar Jun 21 '19 at 01:49
@ErinWilliams did this answer your question? Or is it still giving you the same error? – Robert Todar Jun 21 '19 at 15:28

score 0 · Answer 2 · answered Jun 21 '19 at 18:02

0

this is what worked, and all the macros had to make sure they started with selecting the sheet I was making changes on first

Private Sub CommandButton1_Click()


If Ties.OpPlan.Value = True Then
clearties
ElseIf Ties.Prior.Value = True Then
TieToPrevious
ElseIf Ties.Custom.Value = True Then
CustomTie
ElseIf Ties.NetCase.Value = True Then
NetCaseTie
Else
output = MsgBox("You need to pick a case to tie to", vbOKOnly)
End If


Me.Hide


End Sub

answered Jun 21 '19 at 18:02

Erin Williams

1
1

2

this looks similar to my answer to you. If you found my answer was correct you can mark it accepted. Reposting the same answer might confuse future readers. – Robert Todar Jun 21 '19 at 19:59
If you selected a sheet you probably did it wrong. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `.Select` is a very bad practice and in 99.9 % of the cases it is not necessary to select a sheet. – Pᴇʜ Jul 12 '19 at 08:06

Compile Error on button click in VBA form

2 Answers2

Additional Notes