Userform Only Allowed Characters to be used in Column

[SteynBS]

Hi Guys,

I have a user from with an option button. When the option button is ticked and the user form is closed, the user can only use allowed characters in a selected column. This is a code I have, but it fails. Please help

Private Sub OptionButton4_Click()

Dim rng As Range
If Intersect(Target, Range("C:C")) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("C2:C50000"), Target)
Select Case rng
Case "Y", "N", "N/A", ""
' OK
Case Else
rng.Value = "N"
End Select
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

 

[Jeffrey Mahoney]

Are you saying that the code you provided is NOT allowing users to enter anything but those characters OR you wanting the users to only be able to use those characters?

Either way, the code is not going to work as shown. TARGET is usually a range provided by an event in a sheet when a user either selects a new cell, double clicks a cell, or right clicks a cell.

Maybe you're wanting it to check the cells that have been selected prior to opening the form. In that case you would use SELECTION.

I'm lost at exactly what you need. Are you asking for selected cells to have Data Validation with those choices?

Jeff

 

[SteynBS]

Are you saying that the code you provided is NOT allowing users to enter anything but those characters OR you wanting the users to only be able to use those characters?

Either way, the code is not going to work as shown. TARGET is usually a range provided by an event in a sheet when a user either selects a new cell, double clicks a cell, or right clicks a cell.

Maybe you're wanting it to check the cells that have been selected prior to opening the form. In that case you would use SELECTION.

I'm lost at exactly what you need. Are you asking for selected cells to have Data Validation with those choices?

Jeff

Hi,

This code does not do anything. Throws out an error.

When the option button on the user form is selected and the user form is closed, the user should only be able to apply the values "Y" "N" "N/A" in column C or in this case from C2 to C50000

 

[Jeffrey Mahoney]

Maybe something like this?

Sub OptionButton4_Click()
  
  With Range("C2:C5000").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="Y,N,N/A"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
End Sub

 

[SteynBS]

Maybe something like this?

Sub OptionButton4_Click()
 
  With Range("C2:C5000").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="Y,N,N/A"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
End Sub

Hello, Yes this is perfect, thank you so much.

 

[Jeffrey Mahoney]

Oh good. Can you mark that as the answer please

 

[smozgur]

Hello, Yes this is perfect, thank you so much.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.