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
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: