Userform Help Please

jay111

New Member
Joined
Nov 7, 2009
Messages
2
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

denismccarthy

Board Regular
Joined
Dec 30, 2006
Messages
108
do you have a form started and what is the sheet name of the information you want to display
 

jay111

New Member
Joined
Nov 7, 2009
Messages
2
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

Well-known Member
Joined
Jun 12, 2006
Messages
619
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 (y) can assist if not . Chuf

Code:
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

Board Regular
Joined
Dec 30, 2006
Messages
108
Hi Jay,

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


Code:
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
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,771
Members
412,617
Latest member
mlharris
Top