Add to Dynamic Named Range

  • Thread starter Thread starter Steve
  • Start date Start date
S

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!
 
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
 
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?
 
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?
Click to expand...
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
 
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!
 
Back
Top