Populate a userform from a worksheet

T

Tendresse

I hope someone is able to help me with this problem. It’s driving me crazy!
I’ll try to explain it in the simplest way possible.
I have a workbook with 2 worksheets. Sheet2 contains a table as follows:

A B C
1 Branch Name Address Phone
2 USA LA 123456
3 France Paris 123658
4 England London 125698
etc

In sheet1, the ‘Branch Names’ (USA, France, England, etc) are listed say in
column A. The user can select a cell containing a Branch Name then click on a
control button ‘View Details’. When this button is clicked, I want a userform
to appear displaying the details (Address and Phone) of the selected branch.
Below is the code I’m using. The problem is that every time the userform
appears, it shows the details of the branch that was 'last' selected, not the
branch 'currently' selected. For example, if I last selected USA, and now I
want to see France, I get the details of USA. Then if I select England, I get
the details of France, etc. Can anyone put me in the right direction please?
Here is my code

Private Sub CommandButton3_Click()

' In sheet1, check which branch is selected
Dim x As String
x = ActiveCell.Value

' In sheet2, search for the row number of the row containing the selected
branch
Sheets(“Sheet2â€).Range("Branch").Select
Selection.Find(what:=x).Select

Dim RowNum As Long
RowNum = ActiveCell.Row

' Import the worksheet data onto a userform called ‘View_Form’
View_Form.Show
Set View_Form = Nothing

View_Form.Controls("BranchName").ControlSource = _
Sheets("Sheet2").Cells(RowNum, 1).Address

View_Form.Controls("Address").ControlSource = _
Sheets("Sheet2").Cells(RowNum, 2).Address

View_Form.Controls("Phone").ControlSource = _
Sheets("Sheet2").Cells(RowNum, 3).Address

End Sub

I tried populating the form using this code instead, but I’m still having
the same problem:

View_Form.Controls("Phone").value = _
Sheets("Sheet2").Cells(RowNum, 3).value


I’m using Excel 2003. Many thanks
Tendresse
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top