Worksheet Codename - Crazy Method Error!
 

Hello All -

I'm a VBA beginner, and new to the boards. I've written a program in
VBA (Excel) and everything is going well...except one thing, even after
searching all over the place.
I refer to my worksheets by their codename ( as a good practice).
Excel gives me a 1004 error, "Application-defined or object-defind
error" on both of these these lines (not together in the code):

shInput.Cells(15, 2).Offset(0, varEnabledCount).Select

varNumber = shInput.Cells(18, varActiveCellCol).Value

where shInput is a sheet codename in the workbook where the module
resides, and I already used ThisWorkbook.Activate. However, this line
of code works juuust fine:

shInput.Cells(65,2).Value = 777

Furthermore, they -all- work if I have the shInput sheet activated
before I run the procedure! Why won't they work if I am looking at any
sheet while executing the procedure? Am I nuts? Is VBA broken? Am I
missing something very simple? (I really hope it's the last one).

::kicks Excel::

Thanks in advance!


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile: http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=555046

Worksheet Codename - Crazy Method Error!
 

Works fine. Sure your variables are correctly set?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EphesiansSix"
wrote in message
...

Hello All -

I'm a VBA beginner, and new to the boards. I've written a program in
VBA (Excel) and everything is going well...except one thing, even after
searching all over the place.
I refer to my worksheets by their codename ( as a good practice).
Excel gives me a 1004 error, "Application-defined or object-defind
error" on both of these these lines (not together in the code):

shInput.Cells(15, 2).Offset(0, varEnabledCount).Select

varNumber = shInput.Cells(18, varActiveCellCol).Value

where shInput is a sheet codename in the workbook where the module
resides, and I already used ThisWorkbook.Activate. However, this line
of code works juuust fine:

shInput.Cells(65,2).Value = 777

Furthermore, they -all- work if I have the shInput sheet activated
before I run the procedure! Why won't they work if I am looking at any
sheet while executing the procedure? Am I nuts? Is VBA broken? Am I
missing something very simple? (I really hope it's the last one).

::kicks Excel::

Thanks in advance!


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile:
http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=555046

Worksheet Codename - Crazy Method Error!
 

You can not select a cell on a sheet that is not active. That expalins the
first error. The second error is (as Bob points out) most likely a function
of your variable being not a valid number (such as a decimal, or 0 or greater
than 256)
--
HTH...

Jim Thomlinson


"EphesiansSix" wrote:


Hello All -

I'm a VBA beginner, and new to the boards. I've written a program in
VBA (Excel) and everything is going well...except one thing, even after
searching all over the place.
I refer to my worksheets by their codename ( as a good practice).
Excel gives me a 1004 error, "Application-defined or object-defind
error" on both of these these lines (not together in the code):

shInput.Cells(15, 2).Offset(0, varEnabledCount).Select

varNumber = shInput.Cells(18, varActiveCellCol).Value

where shInput is a sheet codename in the workbook where the module
resides, and I already used ThisWorkbook.Activate. However, this line
of code works juuust fine:

shInput.Cells(65,2).Value = 777

Furthermore, they -all- work if I have the shInput sheet activated
before I run the procedure! Why won't they work if I am looking at any
sheet while executing the procedure? Am I nuts? Is VBA broken? Am I
missing something very simple? (I really hope it's the last one).

::kicks Excel::

Thanks in advance!


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile: http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=555046

Worksheet Codename - Crazy Error!
 

Thanks so much to both of you - you were both right. It makes sense
(though quirky) that I have to activate the sheet first to select the
cell. If I don't select the cell correctly, the variabe
varActiveCellCol which grabs the column of the active (seleted) cell is
not filled properly.

Thanks again!


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile: http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=555046

Worksheet Codename - Crazy Error!
 

More reasons for not selecting cells in code, never have to worry what is
active then.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EphesiansSix"
wrote in message
news:EphesiansSix.29vj7n_1151097902.0449@excelforu m-nospam.com...

Thanks so much to both of you - you were both right. It makes sense
(though quirky) that I have to activate the sheet first to select the
cell. If I don't select the cell correctly, the variabe
varActiveCellCol which grabs the column of the active (seleted) cell is
not filled properly.

Thanks again!


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile:
http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=555046