Range().Select not working

[clmarquez]

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

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

 

[Guest]

Hi,
It worked for me but do the following:

Range("b1").Value = EmpLookup

and remove Range("b1").select

hth

 

[Guest]

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]

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

 

[Dave Peterson]

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