Run userform when cell in range changes to a specific value

[christchaaya]

Hello gurus

I have a range of data validation list cells (c2:c300) and I'd like to call a userform when the user selects "Add..." from the dorpdown list of one of those cells.

I need this part of code because I'm trying to let the user add items to the validation list. If you have any alternative solution you're welcome to share it :D

Regards.

 

[VoG]

Try like this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C300")) Is Nothing And Target.Value Like "Add*" Then
    UserForm1.Show
End If
End Sub

 

[christchaaya]

Try like this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C300")) Is Nothing And Target.Value Like "Add*" Then
    UserForm1.Show
End If
End Sub

Works perfectly! Thank you :D

 

[MickG]

To alter the selected Validation list try these 2 codes "Change Event" To show userform and pass Validation cell addresss, and Userform code with Textbox for Validation addition, and with Command Button to update selected validation list.
Change Event code:-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Count = 1 Then
        If Target.Value = "Add" Then
            Call UserForm2.Mytar(Target)
            UserForm2.Show
        End If
    End If
End Sub

Userform code:-
Based on "Userform2", with a TextBox:- "Textbox1" and a "CommandButton":- CommandButton1"
NB:- Copy this code to the top of your Userform Module

Option Explicit
Dim Ob1 As Object
Sub Mytar(ByRef nOb As Object)
    Set Ob1 = nOb
End Sub
Private Sub CommandButton1_Click()
Dim Temp As String
If Not TextBox1.text = vbNullString Then
    With Ob1.Validation
        Temp = .Formula1
        .Delete
        .Add Type:=xlValidateList, Formula1:=Temp & "," & TextBox1.text  
    End With
MsgBox " New Data Added"
Unload UserForm2
End If
End Sub

 

[christchaaya]

To alter the selected Validation list try these 2 codes "Change Event" To show userform and pass Validation cell addresss, and Userform code with Textbox for Validation addition, and with Command Button to update selected validation list.
Change Event code:-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Count = 1 Then
        If Target.Value = "Add" Then
            Call UserForm2.Mytar(Target)
            UserForm2.Show
        End If
    End If
End Sub

Userform code:-
Based on "Userform2", with a TextBox:- "Textbox1" and a "CommandButton":- CommandButton1"
NB:- Copy this code to the top of your Userform Module

Option Explicit
Dim Ob1 As Object
Sub Mytar(ByRef nOb As Object)
    Set Ob1 = nOb
End Sub
Private Sub CommandButton1_Click()
Dim Temp As String
If Not TextBox1.text = vbNullString Then
    With Ob1.Validation
        Temp = .Formula1
        .Delete
        .Add Type:=xlValidateList, Formula1:=Temp & "," & TextBox1.text  
    End With
MsgBox " New Data Added"
Unload UserForm2
End If
End Sub

Your code seems good to me but my problem is that im working with dependent lists. I got list1 which contains "IN" and "OUT" then i got list "IN" with some items and "OUT" with different items. When the user selects IN in any cell in column b he can chose between INlist items in column c otherwise if he selects OUT in column b he will be able to chose betweekn OUTlist items in column c. What I'm doing is let the user add items to INlist and OUTlist.

Column c validation formula is "INDIRECT($B$2)"