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

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
<[email protected]> 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

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

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

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

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