Running VBA via drop down list

[tabs_here]

Hi

Just wondering if some one could help me out.

i have a workbook with multiple worksheets in it and every page has a droop down list of how many items are required. Then i have used conditional formating to display the desired results. But it is looking like having 50+ different rules on the page as was wondering if i could do it with VBA.

I can write the code but i just need help automatically starting the page. The drop down list goes from 1 to 6, so if 1 is picked then five questions are asked, if 2 is selected then the 5 questions are asked in 2 different rows and so on.

I have seen people use this, but i cant seem to get it to work. Do i need to change "Worksheet" to the name of my sheet?
Sub Master()
Call Worksheet_Change(C2)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
If C2=0 Then code
Else If C2=1 Then code
Else
End If
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try something like this in the ThisWorkbook module in the vba window.

<font face=Courier New><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  <SPAN style="color:#00007F">If</SPAN> Target.Address = "$C$2" <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">'Code goes here, refer to your sheet as 'Sh' for example</SPAN><br>    MsgBox "Sheet name is " & Sh.Name<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If that is not enough to get you going, post back with more specific details of what you have and what you are trying to achieve.

 

[tabs_here]

thanks Peter.

What i would first like to do is clear the contents of some cells.

The first question i ask is "How many racks" with 0-6 in a drop down list in cell C2. So if 0 is picked, nothing is displayed, if 1 is picked then five questions are asked, if 2 is selected then the 5 questions are asked in 2 different rows and so on. (which is all currently controlled by Conditional Formatting)

One of the 5 questions though is answered via a drop down list of 1-3 and this is where i am having the issue. If this question is answered but then value of C2 is changed from 3 to 2 then i cant seem to clear the contents of that cell. I can't just change the text colour of the cell eiother as this value is used to in another sheet.

I also need this to be sheet specific as i will need to do similar things on other sheets

i currently have

Sub Master()
    Call Worksheet_Change(C2)
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$2" Then
    If C2 = 0 Then
      Range("B12").Select
      Selection.ClearContents
      Range("B22").Select
      Selection.ClearContents
      Range("E12").Select
      Selection.ClearContents
    ElseIf C2 = 1 Then
      Range("B22").Select
      Selection.ClearContents
      Range("E12").Select
      Selection.ClearContents
    ElseIf C2 = 2 Then
      Range("E12").Select
      Selection.ClearContents
    End If
  End If
End Sub

If i was to add "(ByVal Sh As Object, " where would i add the ".sh"??

Thanks again for your help
Very much appreciated

 

[tabs_here]

ok so its fine for the sheet but the problem i know have is that if i change the value of cell C2, cells B12, B22 and E12 are all cleared. Can anyone help??

Sub Master()    
Call Worksheet_Change(C2)
End Sub 

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$2" Then    
    If C2 = 0 Then      
      Range("B12").Select      
      Selection.ClearContents      
      Range("B22").Select      
      Selection.ClearContents      
      Range("E12").Select      
      Selection.ClearContents    
    ElseIf C2 = 1 Then      
      Range("B22").Select      
      Selection.ClearContents      
      Range("E12").Select      
      Selection.ClearContents    
    ElseIf C2 = 2 Then      
      Range("E12").Select      
      Selection.ClearContents    
    End If  
  End If 
End Sub

 

[Peter_SSs]

I don't understand your code.
Where is the code placed?
What has Sub Master() got to do with anything? In that code you are using a variable C2 but that variable is not declared anywhere and is not given a value anywhere. In any case, WorksheetChange code is triggered when the worksheet is changed. You say that code is fine but is it actually working for you?

Did you try my code?

 

[tabs_here]

I don't understand your code.
Where is the code placed?
What has Sub Master() got to do with anything? In that code you are using a variable C2 but that variable is not declared anywhere and is not given a value anywhere. In any case, WorksheetChange code is triggered when the worksheet is changed. You say that code is fine but is it actually working for you?

Did you try my code?

this is what i am trying to code:
if the cell value in C2 is "0" then i would like to clear the contents of cells B12, B22 and E12.
if the cell value in C2 is "1" then i would like to clear the contents of cells B22 and E12.
if the cell value in C2 is "2" then i would like to clear the contents of cell E12.

I tried your code and it didnt work, i also found that i dont need the sheet info.

I am running it from the worksheet

 

[Peter_SSs]

And do you want this exact same thing to happen on each worksheet in the workbook if C2 on that worksheet is changed? Your first post seemed to indicate that may be the case.

I tried your code and it didnt work

when you changed cell C2 on one of the worksheets:
Did it give an error message? If so, what error message?
Did it do nothing?
Did it do something unexpected? If so, what?

i also found that i dont need the sheet info.

I'm not quite sure what you mean by this?

 

[tabs_here]

And do you want this exact same thing to happen on each worksheet in the workbook if C2 on that worksheet is changed? Your first post seemed to indicate that may be the case.

when you changed cell C2 on one of the worksheets:
Did it give an error message? If so, what error message?
Did it do nothing?
Did it do something unexpected? If so, what?

at first if C2 changed from 0 to 1 then it was ok, but if you filled in the cell B12 and then changed it to 2 then it would clear B12. But if C2 = 2 it should only clear E12

 

[tabs_here]

Here is the basis of what i am after

Question 4 is answered via a drop down list but if any of the cells have data in them, if C2 changes then all data disappears like below.

If C2 changes from 6 to 5 then i would only like the data in cells H10 to H13 to disappear.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$2" Then
    If C2 = 0 Then
      Range("B4:B7").Select
      Selection.ClearContents
      Range("B10:B13").Select
      Selection.ClearContents
      Range("E4:E7").Select
      Selection.ClearContents
      Range("E10:E13").Select
      Selection.ClearContents
      Range("H4:H7").Select
      Selection.ClearContents
      Range("H10:H13").Select
      Selection.ClearContents
    ElseIf C2 = 1 Then
      Range("B10:B13").Select
      Selection.ClearContents
      Range("E4:E7").Select
      Selection.ClearContents
      Range("E10:E13").Select
      Selection.ClearContents
      Range("H4:H7").Select
      Selection.ClearContents
      Range("H10:H13").Select
      Selection.ClearContents
    ElseIf C2 = 2 Then
      Range("E4:E7").Select
      Selection.ClearContents
      Range("E10:E13").Select
      Selection.ClearContents
      Range("H4:H7").Select
      Selection.ClearContents
      Range("H10:H13").Select
      Selection.ClearContents
    ElseIf C2 = 3 Then
      Range("E10:E13").Select
      Selection.ClearContents
      Range("H4:H7").Select
      Selection.ClearContents
      Range("H10:H13").Select
      Selection.ClearContents
    ElseIf C2 = 4 Then
      Range("H4:H7").Select
      Selection.ClearContents
      Range("H10:H13").Select
      Selection.ClearContents
    ElseIf C2 = 5 Then
      Range("H10:H13").Select
      Selection.ClearContents
    End If
  End If
End Sub

 

[Michael M]

I'm betting Peter will be much more elegant, but in the meantime...and you can change your Range.clearcontents to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c2 As Range
  If Target.Address = "$C$2" Then
    Select Case Target
        Case Is = 0
          Range("B4:B7, B10:B13, E4:E7, E10:E13, H4:H7, H10:H13").ClearContents
        Case Is = 1
               Range("B10:B13, E4:E7, E10:E13, H4:H7, H10:H13").ClearContents
        Case Is = 2
          Range("E4:E7, E10:E13, H4:H7, H10:H13").ClearContents
        Case Is = 3
          Range("E10:E13, H4:H7, H10:H13").ClearContents
        Case Is = 4
          Range("H4:H7, H10:H13").ClearContents
        Case Is = 5
          Range("H10:H13").ClearContents
    End Select
End If
End Sub