VBA argument not optional error userfrom listbox

I wrote the following code for a UserForm but I keep getting an argument not optional error.

Option Explicit
Private Sub CommandButton3_Click()
   Select Case LCase(Me.ListBox2.Text)
   Case "Analysis": Call macro1
   Case "" 
        MsgBox "nothing selected"
   Case Else
        MsgBox Me.ListBox2.Text
   End Select
End Sub

macro 1 code:

Sub macro1()
'
' Macro1 Macro
Sheets.Add
ActiveSheet.Name = "Analysis"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'
Range("A13").Select
ActiveCell.FormulaR1C1 = "Year"
Range("B13").Select
ActiveCell.FormulaR1C1 = "0"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]+1>R9C2,"""",RC[-1]+1),"""")"
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:HS13"), Type:=xlFillDefault 
Range("C13:HQ13").Select
End Sub

1 answer

  • answered 2017-11-15 00:12 Wayne G. Dunn

    First, your method of referencing the ListBox is a problem. See the code below Also, I had to add Sheet Name before Range and then this worked for me...

    Option Explicit
    
    Private Sub CommandButton3_Click()
    Dim i as Integer
    Dim sValue as String
       For i = 0 To Me.ListBox2.ListCount - 1
           If Me.ListBox2.Selected(i) Then
               sValue = ListBox2.List(i)
           End If
       Next i
    
       Select Case LCase(sValue)
          Case "Analysis": Call macro1
          Case "" 
              MsgBox "nothing selected"
          Case Else
              MsgBox sValue.Text
       End Select
    End Sub
    
    Sub macro1()
    '
    ' Macro1 Macro
    Sheets.Add
    ActiveSheet.Name = "Analysis"
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    '
    Sheets("Analysis").Range("A13").Select
    ActiveCell.FormulaR1C1 = "Year"
    Sheets("Analysis").Range("B13").Select
    ActiveCell.FormulaR1C1 = "0"
    Sheets("Analysis").Range("C13").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]+1>R9C2,"""",RC[-1]+1),"""")"
    Sheets("Analysis").Range("C13").Select
    Selection.AutoFill Destination:=Sheets("Analysis").Range("C13:HS13"), Type:=xlFillDefault
    Sheets("Analysis").Range("C13:HQ13").Select
    End Sub