Add to Dynamic Named Range
 

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!

Add to Dynamic Named Range
 

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


On Jan 27, 1:49*pm, Steve wrote:
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!

Add to Dynamic Named Range
 

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?

On Jan 27, 1:21*pm, Don Guillett wrote:
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

On Jan 27, 1:49*pm, Steve wrote:



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!- Hide quoted text -

- Show quoted text -

Add to Dynamic Named Range
 

On Jan 27, 3:08*pm, Steve wrote:
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?

On Jan 27, 1:21*pm, Don Guillett wrote:







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

On Jan 27, 1:49*pm, Steve wrote:

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!- Hide quoted text -

- Show quoted text -
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

Add to Dynamic Named Range
 

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!

On Jan 27, 2:17*pm, Don Guillett wrote:
On Jan 27, 3:08*pm, Steve wrote:



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?

On Jan 27, 1:21*pm, Don Guillett wrote:

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

On Jan 27, 1:49*pm, Steve wrote:

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!- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -