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
 

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:

Forum statistics

Threads
1,081,517
Messages
5,359,234
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top