UserForm with Combo Boxes

[michaeltsmith93]

Hi,

I've created my first userform, and there's something wrong, but I'm not sure what. Please let me know if you need more info than the code that's here.

Here is everything in my VBA Editor. I'd also appreciate it if someone could tell me how to put a box of code in here rather than plain text.

Private Sub CommandButton1_Click()


End Sub


Private Sub Cancel_Click()
Unload Me
End Sub


Private Sub Label3_Click()


End Sub


Private Sub Name_Change()


End Sub


Private Sub OOK_Click()


End Sub


Private Sub OK_Click()
Dim ERow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")


'find first empty row
ERow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for input
If Trim(Me.Name.Value) Or Trim(Me.StudyRole.Value) Or Trim(Me.MatrixRole.Value) Or Trim(Me.Department.Value) = "" Then
MsgBox "Please fill all fields."
Exit Sub
End If


With ws


.Cells(ERow, 1).Value = Me.Name.Value
.Cells(ERow, 2).Value = Me.StudyRole.Value
.Cells(ERow, 4).Value = Me.MatrixRole.Value
.Cells(ERow, 5).Value = Me.Department.Value

End With


Me.Name.Value = ""
Me.StudyRole.Value = ""
Me.MatrixRole.Value = ""
Me.Department.Value = ""


End Sub


Private Sub UserForm_Click()


End Sub


Private Sub UserForm_Initialize()


Dim MRole As Range
Dim Dept As Range


For Each MRole In Range("MatrixRoles")
With Me.MatrixRole
.AddItem MRole.Value
End With
Next MRole


For Each Dept In Range("Depts")
With Me.Department
.AddItem Dept.Value
End With
Next Dept


End Sub

 

[Waimea]

Hi, select # Wrap

 tags around selected text.

 

[michaeltsmith93]

Thank you. Here it is again, but prettier.

Private Sub CommandButton1_Click()


End Sub


Private Sub Cancel_Click()
    Unload Me
End Sub


Private Sub Label3_Click()


End Sub


Private Sub Name_Change()


End Sub


Private Sub OOK_Click()


End Sub


Private Sub OK_Click()
Dim ERow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")


'find first empty row
ERow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'check for input
If Trim(Me.Name.Value) Or Trim(Me.StudyRole.Value) Or Trim(Me.MatrixRole.Value) Or Trim(Me.Department.Value) = "" Then
    MsgBox "Please fill all fields."
    Exit Sub
End If


With ws


    .Cells(ERow, 1).Value = Me.Name.Value
    .Cells(ERow, 2).Value = Me.StudyRole.Value
    .Cells(ERow, 4).Value = Me.MatrixRole.Value
    .Cells(ERow, 5).Value = Me.Department.Value
    
End With


Me.Name.Value = ""
Me.StudyRole.Value = ""
Me.MatrixRole.Value = ""
Me.Department.Value = ""


End Sub


Private Sub UserForm_Click()


End Sub


Private Sub UserForm_Initialize()


Dim MRole As Range
Dim Dept As Range


For Each MRole In Range("MatrixRoles")
    With Me.MatrixRole
        .AddItem MRole.Value
    End With
Next MRole


For Each Dept In Range("Depts")
    With Me.Department
        .AddItem Dept.Value
    End With
Next Dept


End Sub

 

[Waimea]

What is wrong with your code?

 

[michaeltsmith93]

It indicates a compile error, and closing that window takes me to the debugger. It highlights "Private Sub OK_Click()" in yellow and ".Name" in "If Trim(Me.Name.Value) in blue.

 

[Fluff]

I would recommend changing the name of that control to something else, as "Name" is a keyword & the compiler could be getting confused.

 

[michaeltsmith93]

I wondered about that and forgot to look it up. That seems to have corrected one error. I now have another. I'll make a separate post, as per your suggestion earlier, Flump. Thank you for your continued help.

 

[Fluff]

If it's still in connection with this code, you can post it here.
You only need to start a new thread if it's a totally different question

 

[Fluff]

As you have already started a new thread, then you'll need to continue in that.