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