I'm using VBA code in Excel 2007. I am having a strange problem. The
cells have content. Some of them, but not all of them, are selected
from Data Validation lists (drawn from a named range). However, when
I try to access their values in VBA, it says they are empty!''

For example, the simple code:

Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value

results in the output:

Cell value is:

I even tried the following code, to make sure it was not a problem
with the ActiveSheet reference:

ThisWorkbook.Sheets("Form").Cells(2, 7).Value

but I got the same result.

What might be causing this problem??

I also tried saving, closing and reopening the worksheet, with the
same results.

Thanks in advance, Alan

What happen is you do this?

msgbox(ThisWorkbook.Sheets("Form").Cells(2, 7).Value)
msgbox("book : " & thisworkbook.name)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157567

Microsoft Office Help

The first window is blank. The second window shows the correct
Workbook name.

Remove Value.

msgbox(ThisWorkbook.Sheets("Form").Cells(2, 7))
msgbox("book : " & thisworkbook.name)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157567

Microsoft Office Help

No difference. I have never run into this problem before.

Any other ideas?

Make sure you have all the updates to 2007. Lots of bugs. Also there
may be spaces white spaces in the data. I often use theis for
debugging

msgbox(len(ThisWorkbook.Sheets("Form").Cells(2, 7)))

When I 'm not sure if there is data in the cell I check the length of
the data to be sure.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157567

Microsoft Office Help

Run this from the Immediate window:

ThisWorkbook.Sheets("Form").Cells(2, 7).interior.color=vbyellow

Does it highlight the cell you're trying to read from ?

Tim

"Alan" wrote in message
...
No difference. I have never run into this problem before.

Any other ideas?

Joel,
Good idea! It says the length is zero, and it highlights the
wrong cell (although that cell has data in it!).

However, Windows auto update is turned on, and only optional
updates are listed. I am using Excel 12.0.6514.5000 SP2 MSO
(12.0.6425.1000).

I tried saving as a macro-enabled workbook, closing it, then
reopening. Stil has the same problems.

Ideas? Thanks, Alan

Never mind! User error!!