Userform Help Please

[jay111]

Hi All,

I am new to this forum so apologies.

I was wondering if anyone could offer me some assistance / point me in the right direction.

Basically I would like display certain data from a given row in a spreadsheet via a userform which can be opened via a command button and take the required information from the row the user is clicked.

I.e. if the user is in row 2 then the relevant information from that row will be displayed.

Any chance of some assistance ????

Many thanks in advance,

J

 

[denismccarthy]

do you have a form started and what is the sheet name of the information you want to display

 

[jay111]

Hi,

I have been playing around with forms all day so have a few bits and pieces.

Say that the sheet containing all the data = sheet1.

Once I have a form wanting to display say "name", "age", "Height" how do I take the information from sheet1 and put in the relevant place on the form ???

Any help would be great.

 

[chuf]

Hi Jay.

I'm posting this code with a disclaimer that i can't explain how it works. .. its from one of the many board members who helped me with my latest userform.

Hope you can adapt it.. or one of the others here can assist if not . Chuf

Private Sub cmbEdit_Click()
Dim varMatch
Application.ScreenUpdating = False
With Sheets("ABC Recharge")
varMatch = Application.Match(Me.txtRil, .Range("F:F"), 0)
If IsError(varMatch) Then
   MsgBox "Ref ID not found"
Else
   Me.cboTeamr = .Cells(varMatch, "A").Value
   Me.txtMan = .Cells(varMatch, "B").Value
   Me.txtVac = .Cells(varMatch, "C").Value
   Me.txtPub = .Cells(varMatch, "D").Value
   Me.txtDat = Format(.Cells(varMatch, "E").Value, "dd/mm/yy")
   Me.txtRef = .Cells(varMatch, "F").Value
   Me.txtFin = .Cells(varMatch, "G").Value
   Me.txtCos = Format(.Cells(varMatch, "H").Value, "###0.00")
 End If
End With
Application.ScreenUpdating = True
End Sub

 

[denismccarthy]

Hi Jay,

Try this code if you find the match function won't work

Private Sub cmbEdit_Click()
Dim MyRow As Integer
Dim LastRow As Integer
Dim MySheet As String
  
Application.ScreenUpdating = False
 
MySheet = "ABC Recharge"
LastRow = 1000 ' will look for MyRow as far as row 1000
MyRef = Me.txtRil
 
' start to look in each cell in Column F for MyRow
For i = 1 To LastRow
If Sheets(MySheet).Range("F" & i).Value = MyRef Then
MyRow = i
Else
End If
If MyRow > 0 Then GoTo EndLoop
Next i
 
EndLoop:
 
'Check a match was found
If MyRow = 0 Then
MsgBox "Ref ID not found"
Exit Sub
Else
 
End If
 
Me.cboTeamr = Sheets("ABC Recharge").Cells(MyRow, 1).Value
Me.txtMan = .Cells(MyRow, 2).Value
Me.txtVac = .Cells(MyRow, 3).Value
Me.txtPub = .Cells(MyRow, 4).Value
Me.txtDat = Format(.Cells(MyRow, 5).Value, "dd/mm/yy")
Me.txtRef = .Cells(MyRow, 6).Value
Me.txtFin = .Cells(MyRow, 7).Value
Me.txtCos = Format(.Cells(MyRow, 8).Value, "###0.00")
 
 Application.ScreenUpdating = True
End Sub