Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

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

UncleSporkyUncleSporky Registered User regular
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.

UncleSporky on
3DS Friend Code: 0989 - 1731 - 9504
Nintendo Network ID: unclesporky

Posts

  • Steel FireSteel Fire Gunboat Diplomat PAI MarketingRegistered User regular
    edited 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
    

  • UncleSporkyUncleSporky Registered User regular
    edited 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. :P

    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.

    3DS Friend Code: 0989 - 1731 - 9504
    Nintendo Network ID: unclesporky
  • Michael HMichael H Registered User
    edited 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. :P

    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
    

    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.

Sign In or Register to comment.