Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
Please help! I have a command button on a worksheet, that when the user highlights a cell with an employee name in it and presses the button, it takes them to a different worksheet, inputs the highlighted cell's value into cell B1 (which is linked to a combobox) and then calls a macro. The code for the command button is as follows: Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select On Error Resume Next Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub My problem is that when the sub is stepped through, the Range("B1").Select line does not work (everything else does), and so the name gets inputted into whatever cell happens to be highlighted in the worksheet. Can anyone tell me why that line won't work? Is there another way to force selection of cell B1? I am using Excel 2003... Thanks, Kez -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=494049 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
I'm not sure - but I'd get rid of that On Error line while checking it.
Does this work? Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab with worksheets("Employee Summary") .Range("B1").value = activecell.value .select end with Call Load_Emp_Summary End Sub On Fri, 16 Dec 2005 05:36:49 -0000, clmarquez wrote: Please help! I have a command button on a worksheet, that when the user highlights a cell with an employee name in it and presses the button, it takes them to a different worksheet, inputs the highlighted cell's value into cell B1 (which is linked to a combobox) and then calls a macro. The code for the command button is as follows: Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select On Error Resume Next Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub My problem is that when the sub is stepped through, the Range("B1").Select line does not work (everything else does), and so the name gets inputted into whatever cell happens to be highlighted in the worksheet. Can anyone tell me why that line won't work? Is there another way to force selection of cell B1? I am using Excel 2003... Thanks, Kez -- ------------------ Darren |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
Hi,
It worked for me but do the following: Range("b1").Value = EmpLookup and remove Range("b1").select hth "clmarquez" wrote: Please help! I have a command button on a worksheet, that when the user highlights a cell with an employee name in it and presses the button, it takes them to a different worksheet, inputs the highlighted cell's value into cell B1 (which is linked to a combobox) and then calls a macro. The code for the command button is as follows: Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select On Error Resume Next Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub My problem is that when the sub is stepped through, the Range("B1").Select line does not work (everything else does), and so the name gets inputted into whatever cell happens to be highlighted in the worksheet. Can anyone tell me why that line won't work? Is there another way to force selection of cell B1? I am using Excel 2003... Thanks, Kez -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=494049 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
Suggested code follows. Note that it is not necessary to activate the target
sheet. If you in fact want to do this then say include the line ws.Activate just before the Load_Emp_Summary routine is called:- Private Sub CommandButton1_Click() Dim ws As Worksheet Set ws = Sheets("Employee Summary") ws.Range("B1") = ActiveCell Call Load_Emp_Summary End Sub Your code with correction follows. Note the commented out line will return the original sheet's name proving that Range("B1") still references the original sheet even though another sheet is active. I assume this is because the identity is obtained when the code is compiled. You needed the "On Error Resume Next" line becasue you can't select cell B1 in the original sheet once sheet Employee Summary is active. However, if you qualify it with ActiveSheet then it will work:- Private Sub CommandButton1_Click() Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select 'MsgBox Range("B1").Parent.Name ActiveSheet.Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub Regards, Greg "clmarquez" wrote: Please help! I have a command button on a worksheet, that when the user highlights a cell with an employee name in it and presses the button, it takes them to a different worksheet, inputs the highlighted cell's value into cell B1 (which is linked to a combobox) and then calls a macro. The code for the command button is as follows: Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select On Error Resume Next Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub My problem is that when the sub is stepped through, the Range("B1").Select line does not work (everything else does), and so the name gets inputted into whatever cell happens to be highlighted in the worksheet. Can anyone tell me why that line won't work? Is there another way to force selection of cell B1? I am using Excel 2003... Thanks, Kez -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=494049 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
Yeah, I am trying to learn Excel VB on the fly, so my code writing is marginal at best right now. Thanks for the corrections... -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=494049 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range().Select not working
The problem was that when you have code behind a worksheet (like your
commandbutton1_click module), any unqualified ranges belong to the sheet that contains the code: So "Range("B1").select" was refering to the sheet with the code. You selected "employee summary" first. So that was the activesheet. But you can only select cells on the activesheet. So it blew up. You could have done this (very ugly): Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select sheets("employee summary").Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub But it's much better not to select anything. sheets("employee summary").range("b1").value = emplookup clmarquez wrote: Please help! I have a command button on a worksheet, that when the user highlights a cell with an employee name in it and presses the button, it takes them to a different worksheet, inputs the highlighted cell's value into cell B1 (which is linked to a combobox) and then calls a macro. The code for the command button is as follows: Private Sub CommandButton1_Click() 'Employee Detail Button ' Lookup selected employee and load data on Employee Summary tab Dim EmpLookup As Variant EmpLookup = ActiveCell.Value Sheets("Employee Summary").Select On Error Resume Next Range("B1").Select ActiveCell.Value = EmpLookup Call Load_Emp_Summary End Sub My problem is that when the sub is stepped through, the Range("B1").Select line does not work (everything else does), and so the name gets inputted into whatever cell happens to be highlighted in the worksheet. Can anyone tell me why that line won't work? Is there another way to force selection of cell B1? I am using Excel 2003... Thanks, Kez -- clmarquez ------------------------------------------------------------------------ clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386 View this thread: http://www.excelforum.com/showthread...hreadid=494049 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range("a1").select not working | Excel Discussion (Misc queries) | |||
range select not working with small sets | Excel Programming | |||
Select Range - using Offset ( 40 ,0 ) - not working | Excel Programming | |||
Select Sheet then Select Range | Excel Programming | |||
Range().Select Not Working | Excel Programming |