Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve
 
Posts: n/a
Default Often-Used Code not working in a new Workbook

I have the following code to transfer an option from a combobox to a
cell on one sheet. I've used this code several times before in other
workbooks, and it works absolutely fine, but, for some reason, in my
current project, it just comes up as an error and the
'Range("G11").Select' line is highlighted in yellow. Is there some
obvious or uderlying reason for this, does anyone know?

The code is:

Private Sub ComboBox1_Change()
Worksheets("How").Activate
Range("G11").Select
ActiveCell.Value = ComboBox1.Value
Worksheets("Statskeeper").Activate
End Sub

I've tried the same code in a commandbutton_click as well, but with the
same result.

Any ideas would be greatly appreciated.

Thanks

Steve

  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

is cell G11 protected?

BTW not sure that it will solve your problem but you could re-write your
code as follows:

Private Sub ComboBox1_Change()
Worksheets("How").Range("G11").Value = ComboBox1.Value
Worksheets("Statskeeper").Activate
End Sub



Cheers
JulieD


"Steve" wrote in message
oups.com...
I have the following code to transfer an option from a combobox to a
cell on one sheet. I've used this code several times before in other
workbooks, and it works absolutely fine, but, for some reason, in my
current project, it just comes up as an error and the
'Range("G11").Select' line is highlighted in yellow. Is there some
obvious or uderlying reason for this, does anyone know?

The code is:

Private Sub ComboBox1_Change()
Worksheets("How").Activate
Range("G11").Select
ActiveCell.Value = ComboBox1.Value
Worksheets("Statskeeper").Activate
End Sub

I've tried the same code in a commandbutton_click as well, but with the
same result.

Any ideas would be greatly appreciated.

Thanks

Steve



  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

JulieD fixed the problem by not selecting the cell.

When you have an unqualified range in a routine in a General module, it refers
to the activesheet.

But when that routine is in a worksheet module, that unqualified range refers to
the sheet that owns the code.

So you could qualify things:

Private Sub ComboBox1_Change()
Worksheets("How").Activate
worksheets("how").Range("G11").Select
ActiveCell.Value = ComboBox1.Value
Worksheets("Statskeeper").Activate
End Sub

But JulieD's code is much better cleaner.

And if this combobox is on the StatsKeeper worksheet, you can drop that line
from JulieD's code. (Since she didn't select the other sheet, she doesn't have
to return to it.



Steve wrote:

I have the following code to transfer an option from a combobox to a
cell on one sheet. I've used this code several times before in other
workbooks, and it works absolutely fine, but, for some reason, in my
current project, it just comes up as an error and the
'Range("G11").Select' line is highlighted in yellow. Is there some
obvious or uderlying reason for this, does anyone know?

The code is:

Private Sub ComboBox1_Change()
Worksheets("How").Activate
Range("G11").Select
ActiveCell.Value = ComboBox1.Value
Worksheets("Statskeeper").Activate
End Sub

I've tried the same code in a commandbutton_click as well, but with the
same result.

Any ideas would be greatly appreciated.

Thanks

Steve


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook startup condition wikegolfa Excel Discussion (Misc queries) 3 December 16th 04 04:11 PM
Error message opening Excel97 workbook cobbler Excel Discussion (Misc queries) 2 December 11th 04 06:31 PM
Stubborn toolbars in Excel 007 Excel Discussion (Misc queries) 9 December 11th 04 03:02 PM
how can I duplicate or copy a workbook then divide selected cells. macros excel... duplication and calculat Excel Discussion (Misc queries) 1 November 29th 04 04:16 PM
copy\paste from one workbook to another Mark Excel Discussion (Misc queries) 0 November 25th 04 11:55 PM


All times are GMT +1. The time now is 06:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"