1

I am trying create a sign in userform for sports club that I help run but I am having trouble adding to a Dynamic Array.

    Private Sub UserForm_Initialize()

Dim SignedInNames() As String
Dim NumberSignedIn As Integer
    NumberSignedIn = 1

ReDim SignedInNames(NumberSignedIn + 1)


Dim Names() As Variant
Names = Range("OFFSET('People Info'!$A$1,1,0,COUNTA('People Info'!$A:$A)-1,1)")

PersonNameComboBox.List = Names

SignedInListBox.List = SignedInNames
End Sub

Private Sub SignInCommandButton_Click()

SignedInNames = Add(PersonNameComboBox.Value) 'The code crashers here
NumberSignedIn = NumberSignedIn + 1

End Sub

I've changed it so now it is

Private Sub UserForm_Initialize()

Dim SignedInNames() As String
Dim NumberSignedIn As Integer
    NumberSignedIn = 1

ReDim SignedInNames(0 To NumberSignedIn)


Dim Names() As Variant
Names = Range("OFFSET('People Info'!$A$1,1,0,COUNTA('People Info'!$A:$A)-1,1)")

PersonNameComboBox.List = Names

SignedInListBox.List = SignedInNames
End Sub

Private Sub SignInCommandButton_Click()

ReDim Preserve SignedInNames(0 To NumberSignedIn)
SignedInNames(NumberSignedIn) = PersonNameComboBox.Value
NumberSignedIn = NumberSignedIn + 1
'ReDim SignedInNames(o To NumberSignedIn)
SignedInListBox.List = SignedInNames
'SignedInNames = Add(PersonNameComboBox.Value)
'SignedInNames(NumberSignedIn) = PersonNameComboBox.Value
'NumberSignedIn = NumberSignedIn + 1

End Sub

I am now getting an error where when it gets to the line

ReDim Preserve SignedInNames(0 To NumberSignedIn)

it is showing the error "Variable not defined"

  • 1
    What's the actual error Excel throws? – Ƭᴇcʜιᴇ007 Jan 2 '16 at 16:19
  • Compile error: Sub or Function not defined – Matthew Johnston Jan 2 '16 at 16:22
  • Where/when does that error appear? Does it give you a line number? – Ben N Jan 2 '16 at 16:38
  • do you have add sub??? – Máté Juhász Jan 2 '16 at 16:40
  • the code crashers on the line I marked with "'The Code Crashers here" and the code there is all the code I am using in that User Form – Matthew Johnston Jan 2 '16 at 16:42
0

I solved the original problem by changing the way I setup the array. Then I fixed the second problem by making the variables I was using global.

Dim SignedInNames() As String
Dim NumberSignedIn As Integer
Dim Names() As Variant

Private Sub UserForm_Initialize()


NumberSignedIn = 1

ReDim SignedInNames(0 To NumberSignedIn)


Dim Names() As Variant
Names = Range("OFFSET('People Info'!$A$1,1,0,COUNTA('People Info'!$A:$A)-1,1)")

PersonNameComboBox.List = Names

SignedInListBox.List = SignedInNames
End Sub

Private Sub SignInCommandButton_Click()



ReDim Preserve SignedInNames(0 To NumberSignedIn)
SignedInNames(NumberSignedIn) = PersonNameComboBox.Value
NumberSignedIn = NumberSignedIn + 1
SignedInListBox.List = SignedInNames


End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.