Mouse
Posted by John on June 08, 2000 2:01 AM
Is there a way to get a separate sheet to update to the values on a separate page based on the block that is highlighted.
Example if i have a page that has an order number in column a, and the cusomer name in b, and address in columns c,d,e,and f and phone number in g and i want a form on page 2 to update based on clicking the order number or something. I thought about a dropdown menu but there are over 600 and typeing in the number it would be kind of easy to make a mistake because my goal is to eventually hide the list page. And get a form that updates the list page. Anyone that has a similar form/sheet i would be very happy for your help.
Posted by Ryan on June 11, 0100 4:20 PM
Here is a possible solution. I recommend naming the cells that are receiving the information. In this code that I'm posting, it is easy to see what i have named the cells. You can change these if you like or keep them. You must also change the name "Sheet2" in this code to match what you actual sheet name is. There are many ways to call this procedure, I have just chosen one, to call it when you select a new order number. If an order number is not selected it tells you it's not a valid entry. This would probably be annoying if you are trying to enter new data though. So it might be better to leave out the "Worksheet_SelectionChange" procedure and assign the macro to a new toolbar button. If you don't know who to do this, email me or post another question. Hope this helps.
Sub UpdateOtherSheet()
If Left(ActiveCell.Address, 2) <> "$A" Then
MsgBox "Invalid Starting Cell"
Exit Sub
End If
Application.ScreenUpdating = False
Sheets("Sheet2").Range("OrderNumber").Value = ActiveCell.Value
Sheets("Sheet2").Range("Customer").Value = ActiveCell.Offset(0, 1).Value
Sheets("Sheet2").Range("Address1").Value = ActiveCell.Offset(0, 2).Value
Sheets("Sheet2").Range("Address2").Value = ActiveCell.Offset(0, 3).Value
Sheets("Sheet2").Range("Address3").Value = ActiveCell.Offset(0, 4).Value
Sheets("Sheet2").Range("Address4").Value = ActiveCell.Offset(0, 5).Value
Sheets("Sheet2").Range("PhoneNum").Value = ActiveCell.Offset(0, 6).Value
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call UpdateOtherSheet
End Sub
