Excel VB programming question - skipping cells that lack a certain property

UncleSporky · February 2011

It's been a long time since I programmed anything or messed with VB. I've googled my problem and narrowed it down, but I'd still like some help if anyone here knows their stuff!

What I have is an Excel spreadsheet that someone has put a lot of text into the data validation input messages (the text that pops up when you click a cell). I would like to extract the input messages and put them into cells for easier editing. In its simplest form, what I want to do is this:

Range("a1").Value = Range("a1").Validation.InputMessage

This works just fine if an input message has been defined. But if a cell has no input message, I get a "Run-time error '1004': application-defined or object-defined error." I'm trying to access a property that isn't simply null, it just doesn't exist at all.

What code can I use to check whether .Validation.InputMessage exists, and if so, copy it to a cell?

If you want to be really nice, I'm also doing it in a loop straight down the column and haven't looked up the best way to do that yet.

Steel Fire · February 2011

This may be as simple as putting in an

On Error Resume Next

statement.

However, I'm only slightly better than a novice myself and that would be a real ugly solution. Seems like and If statement would be cleaner and faster. Even at that, I think you want to give it some limitation on the overall range of cells to look at, or it's going to look at every one in the column.

Since you're only looking in a single column and if you're copying to a known position, I would look at something along the lines of: (won't guarantee this syntax is going to be perfect or will even work since I don't have my normal resources to refer to and I haven't work with the validation stuff)

Dim i as integer Dim n as integer For i = 1 to n (use n if you want it to check every row in the column, use the row number you want it to stop at otherwise) If Range("a" & i).Value = Range("a" & i).Validation.InputMessage 'copy message code here, using the copy to range as, for example, ("B" & i) next i[/CODE][CODE]
Dim i as integer
Dim n as integer

For i = 1 to n
(use n if you want it to check every row in the column, use the row number you want it to stop at otherwise)

If Range("a" & i).Value = Range("a" & i).Validation.InputMessage

'copy message code here, using the copy to range as, for example, ("B" & i)

next i[/CODE]

UncleSporky · February 2011

On Error Resume Next works perfectly, actually. I don't really care if it's sloppy code, I just want the validation data to be editable.

The If statement doesn't work though, because .InputMessage isn't a valid item unless there is text in it. It doesn't resolve to "", the .InputMessage property doesn't exist at all and that's what causes the error.

Oh well, I got it to work, thanks a lot.

Michael H · February 2011

UncleSporky wrote: »

On Error Resume Next works perfectly, actually. I don't really care if it's sloppy code, I just want the validation data to be editable.

The If statement doesn't work though, because .InputMessage isn't a valid item unless there is text in it. It doesn't resolve to "", the .InputMessage property doesn't exist at all and that's what causes the error.

In that case what can work in VBA is:

If yourRange.Validation.InputMessage Is Not Nothing Then ' Code goes here End If[/CODE] Using what is essentially a double negative to validate something makes my eyes bleed, but it's part of the fun of working within VBA.[CODE] If yourRange.Validation.InputMessage Is Not Nothing Then
' Code goes here
End If[/CODE]

Using what is essentially a double negative to validate something makes my eyes bleed, but it's part of the fun of working within VBA.

Penny Arcade

Excel VB programming question - skipping cells that lack a certain property

Posts