Need help with Sheet module: Change in case select:

[pedie]

From sheet1.range("H12:H17") if i have validation...if i select 1 then run macro1 if two is selected then macro2 or nothing: This should work with i change cell value..


Thanks for helping!
Pedie

 

[Progrower]

Paste the code into the sheets code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    For i = 12 To 17
    If Range("H" & i).Value = 1 Then
        Call Macro1
    Else
    If Range("H" & i).Value = 2 Then
        Call Macro2
    End If
Next i
End Sub

 

[pedie]

Eric, was caught up @ work and now when i try to work this out next i is where it is giving error:
I am very confused because I dont really know what is going on

when i select one in h12 for example i want it to Offset(0, -3) then insert a validation =table1 (is a range name)....if "two" then table2 range validation...
if in h13 one then validation =table1 (is a range name)....if "two" then table2 range ...it goes on till H17...
I have also put my code below..pls shorten it if possible...

anyone pls help.

Thanks again for helping!

Pedie

If Range("H12").Value = "One" Then
 Range("H12").Select
  ActiveCell.Offset(0, -3).Select
  Call Macro1
  Else
If Range("H12").Value = "Two" Then
 Range("H12").Select
  ActiveCell.Offset(0, -3).Select
  Call Macro2
'.......till H17
End If
End Sub

Sub Macro1()
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Sub Macro2()
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Sub tyyt()

 

[Progrower]

paste this in the worksheets vba code module

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("H12").Value = "One" Then
 Range("H12").Select
  ActiveCell.Offset(0, -3).Select
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
  Else
If Range("H12").Value = "Two" Then
 Range("H12").Select
  ActiveCell.Offset(0, -3).Select
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
End If
End Sub

 

[pedie]

Eric, thank you but what will happen if the cell is blank?
Can i use like if ("").value = "' then exit sub?

And what you have given right now if only for h12 i want this to cont till h17 from h12...but seperately for each...

The thing is that i dont want same validation to b across the range so when a user select the drop down then offset (....pls see above) and put the validation accordingly depending on h col value....

I know I might have ask for more but I'll be really glad if you can help me work this out....

Thanks
Pedie

 

[pedie]

Sub lastry()
s = 12
e = 17
c = 8
For chk = s To e
  If Cells(chk, "H").Value = "One" Then Call Macro1 Else Call Macro2
  If Cells(chk, "H").Value = "" Then Exit Sub
  
 Next chk
End Sub

 

[Progrower]

Try this on a copy of your workbook

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 12 To 17
If Range("H" & i).Value = "One" Then
 Range("H" & i).Select
  ActiveCell.Offset(0, -3).Select
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
  Else
If Range("H" & i).Value = "Two" Then
 Range("H" & i).Select
  ActiveCell.Offset(0, -3).Select
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(0, 1).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=table2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End If
End If
Next i


End Sub

 

[pedie]

I also cant hear What i am looking @ Eric.


Thanks alot: That is perfect now..I can shorten the other part of it.

Thank you sooo much!

Regards Pedie