Add to Dynamic Named Range

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!
 
D

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
 
S

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?
 
D

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
 
S

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top