VBA creation of Data Validation message content

officemhwalmsley

New Member
Joined
Apr 5, 2006
Messages
3
The code below is intended to loop through a sheet containing a template for users to fill in. Some cells have data validation from lists which are indicated by "Choose from" in column B. Others have "See help" in column B. The help text is currently on a separate "Help" tab.

I've created a VBA to populate the Input Title and Input Message properties of the Data Validation feature for input cells on the "See help" rows. The VBA correctly populates the .InputTitle and .InputMessage properties for the first time through the While ... Wend loop. When setting the .InputTitle the second time through the code, it returns a 1004 Run-time error.

I've seen the same thing when trying to use a variable with a concatenated string, e.g.
trying to set the .InputTitle to szHelpTitle where setting

szHelpTitle = ActiveCell.Value & " help text"

also returns the same error.

The first iteration works. Can anyone see why the second fails?

Thanks

Martyn

Full Code below

Code:
Sub SetupHelpText()

    Sheets("Assessment").Activate
    
    Range("A1").Activate
    
    Set sSeeHelp = Cells.Find("See help", , xlValues, xlPart, xlByColumns, xlNext)

    While Not sSeeHelp Is Nothing
    
'        aFoundAddress = sTextAddress.Address
        Range(sSeeHelp.Address).Offset(0, -1).Activate
        
        szSearchText = ActiveCell.Value
        szHelpTitle = ActiveCell.Value
        Sheets("Help").Activate
        Range("A1").Activate
        
        Set sTextAddress = Cells.Find(szSearchText, , xlValues, xlPart, xlByColumns, xlNext)
        aFoundAddress = sTextAddress.Address
        Range(aFoundAddress).Activate
    '    szHelpTitle = ActiveCell.Value
        szHelpContent = ActiveCell.Offset(0, 1).Value
        
        Sheets("Assessment").Activate
        
        Range(sSeeHelp.Address).Offset(0, 1).Activate
       
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
            :=xlBetween
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = szHelpTitle
            .ErrorTitle = ""
            .InputMessage = szHelpContent
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        
        Set sSeeHelp = Cells.Find("See help", Range(sSeeHelp.Address), xlValues, xlPart, xlByColumns, xlNext)
    Wend
        
End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top