Add to Dynamic Named Range

[Steve]

Hi all. I have a dynamic named range in Sheet1 A2:A100. I would like
to call a macro to pop an input box asking for a new value, and then
write that value in the next available cell in my named range. Any
idea how I can accomplish this?

Thanks!

 

[Don Guillett]

You don't need a named range for this

Sub msgboxvaluetonextrowSAS()
Dim ans As String
ans = InputBox("Enter Value", vbOKCancel)
Cells(2, "a").End(xlDown).Offset(1) = ans
End Sub

 

[Steve]

Thanks Don!

One follow up question - is there a way to NOT allow the user to enter
the words "request" or "issue" in the string, displaying an error if
they do?

 

[Don Guillett]

Thanks Don!

One follow up question - is there a way to NOT allow the user to enter
the words "request" or "issue" in the string, displaying an error if
they do?

Sub msgboxvaluetonextrowSAS()
Dim ans As String
ans = InputBox("Enter Value", vbOKCancel)
If UCase(ans) = "REQUEST" Or UCase(ans) = "ISSUE" Then
MsgBox "Not allowed"
Else
Cells(1, "a").End(xlDown).Offset(1) = ans
End If
End Sub

 

[Steve]

Thanks Don! Is there a way to use a "contains" function as opposed to
an equal to? For example, I also need to prevent the user from
entering "Compliance Request".

Thanks!