Hi, an Ozgrid user StephenR has kindly solved this for me.

(visit http://www.ozgrid.com/forum/showthread.php?t=173908 )

Below is the corrected code that works wonderfully:


Private Sub CommandButton1_Click()

Dim Ctrl As Control

Set Ctrl = ActiveControl

If TypeName(Ctrl) = "ListBox" Then 'when the cursor is active in a ListBox, enter this data into the spreadsheet
Select Case Ctrl.name
Case Is = "ListName"
MsgBox Ctrl.name & " selection = " & Ctrl.Value 'This adds a message box that confirms the selection
End Select
End If

Set Ctrl = ActiveControl

If TypeName(Ctrl) = "TextBox" Then 'when the cursor is active in a TextBox, enter this data into the spreadsheet
Select Case Ctrl.name
Case Is = "TextName"
MsgBox Ctrl.name & " selection = " & Ctrl.Value 'This adds a message box that confirms the selection
End Select
End If

ActiveCell.Value = TextName.Text & ListName.Text 'paste textbox selection AND listbox selection into the active cell
Me.Hide 'closes the UserForm when the action is performed

Set Cell = Columns(1).Find(What:=Ctrl.Value, After:=Range("A1"), LookAt:=xlWhole, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 'this next code searches up the sheet to find a name match
If Not Cell Is Nothing Then
Cell.Offset(0, 1).Resize(, 4).Copy Range("A" & Rows.Count).End(xlUp).Offset(, 1) 'then pastes a range from the matching row to the current row

End If
End Sub


I needed to amend my existing button code and there was no need for the extra macro.