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]

My suggestion would be this. With the layout & formatting as shown in your linked images

1. Select B4:B7 and name the range Ans_1, Select B10:B13 and name the range Ans_2, E4:E7 is named Ans_3 etc.

2. Select A4:B7 and apply Conditional formatting using the formula =$C$2<1 and set the format to white font and no borders.
Select A10:B13 and apply Conditional formatting using the formula =$C$2<2 and set the format to white font and no borders.
Repeat this pattern for the other sections.

3. Use this Worksheet_Change code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long
  
  If Target.Address = "$C$2" Then
    Application.EnableEvents = False
    For i = Target.Value + 1 To 6
      Range("Ans_" & i).ClearContents
    Next i
    Application.EnableEvents = True
  End If
End Sub

 

[Michael M]

Hi Peter
As predicted....much more elegant....and expected.....

 

[tabs_here]

Thankyou Michael, this has fixed the issue.

Very much appreciated

 

[Michael M]

Thanks, but I think you'll find Peters code much quicker !!...

 

[tabs_here]

My suggestion would be this. With the layout & formatting as shown in your linked images

1. Select B4:B7 and name the range Ans_1, Select B10:B13 and name the range Ans_2, E4:E7 is named Ans_3 etc.

2. Select A4:B7 and apply Conditional formatting using the formula =$C$2<1 and set the format to white font and no borders.
Select A10:B13 and apply Conditional formatting using the formula =$C$2<2 and set the format to white font and no borders.
Repeat this pattern for the other sections.

3. Use this Worksheet_Change code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long
  
  If Target.Address = "$C$2" Then
    Application.EnableEvents = False
    For i = Target.Value + 1 To 6
      Range("Ans_" & i).ClearContents
    Next i
    Application.EnableEvents = True
  End If
End Sub

Thanks Peter i never thought of using ranges. I cant use conditional formating on cells B4:B7 to make the font white as the value in B7 is used in another sheet.

 

[Peter_SSs]

I cant use conditional formating on cells B4:B7 to make the font white as the value in B7 is used in another sheet.

I think that you may not have understood (or tried?) my suggestion.

To start with, all the ranges are formatted with normal text and borders as you had in your sheet.

The Conditional Formatting as I suggested it only changes fonts to white for question sections that are not being used.
When the question is being used, then the Conditional Formatting will not apply and the section will once again appear with normal text and the relevant borders.
For example, A2:B7 would only be formatted with white text if C2 was 0 or blank - that is at a time when that first section was not being used.
And if C2 was 0, your code in post #9, Michael's in post #10 and mine in post #11 would have cleared any values from B7 anyway.

Finally, even if a cell has data formatted with white text, it would stop that value being used elsewhere.

If you aren't able to follow the steps that I have tried to describe, you could PM me with your email address and I could provide you with a working example file.

 

[tabs_here]

I think that you may not have understood (or tried?) my suggestion.

To start with, all the ranges are formatted with normal text and borders as you had in your sheet.

The Conditional Formatting as I suggested it only changes fonts to white for question sections that are not being used.
When the question is being used, then the Conditional Formatting will not apply and the section will once again appear with normal text and the relevant borders.
For example, A2:B7 would only be formatted with white text if C2 was 0 or blank - that is at a time when that first section was not being used.
And if C2 was 0, your code in post #9, Michael's in post #10 and mine in post #11 would have cleared any values from B7 anyway.

Finally, even if a cell has data formatted with white text, it would stop that value being used elsewhere.

If you aren't able to follow the steps that I have tried to describe, you could PM me with your email address and I could provide you with a working example file.

Sorry just to clarify, why would A2:B7 need to have conditional formatting if B2:B7 has all the data cleared? Wouldnt A2:A7 just have to have the conditional formatting??

Finally, even if a cell has data formatted with white text, it would stop that value being used elsewhere.

so if i had some code that changes the colour of the text, then that text can not be used to run other conditional formatting or code??

 

[Peter_SSs]

Sorry just to clarify, why would A2:B7 need to have conditional formatting if B2:B7 has all the data cleared? Wouldnt A2:A7 just have to have the conditional formatting??

B4:B7 wouldn't really need to have the font changed to white but I thought that you wanted the borders around those cells to disappear if that section wasn't being used.

If that is the case B4:B7 needs some Conditional Formatting and it seemed easiest to me to do the whole section A4:B7 together in one go with the same CF rather than have one lot of CF for A4:A7 (white font & no borders) and a different lot of CF for B4:B7 (no borders). It doesn't matter that the font in B4:B7 is white when there is nothing in there anyway, and those cells will return to normal font colour as soon as that section becomes available because of a change in the C2 value.

Finally, even if a cell has data formatted with white text, it wouldn't stop that value being used elsewhere.

so if i had some code that changes the colour of the text, then that text can not be used to run other conditional formatting or code??

Ooops, that was a bad typo from me, fixed above. Sorry about the confusion on that.

 

[tabs_here]

ok i understand, thanks again