tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi there,
Before I go back to the drawing board, can anyone see whether they can get this VBA code to work?
Basically, I have two drop down lists. One is "No. of Investments" and the other is "No. of Partners". I would like certain rows to hide, depending on the result of both drop down lists.
Each case below shows the change in "No. of Investments" and then looks at the drop down list of "No. of Partners" and hides the rows accordingly (well, it's supposed to atleast).
Often, drop down list "No. of Partners" will not be engaged and will remain on "Please Select", so the first line of code will be used depending on the "No. of Investments" selected.
Currently the error being returned is "Compile Error - Argument no Optional" and it highlights the word "Union" on the first "Set R" line.
Drop down lists look like this;
No._Investments = Please Select,0,2,4,6,8,10
No._Partners = Please Select,2,3,4,5,6,7,8,9,10
Thanks for looking.
Before I go back to the drawing board, can anyone see whether they can get this VBA code to work?
Basically, I have two drop down lists. One is "No. of Investments" and the other is "No. of Partners". I would like certain rows to hide, depending on the result of both drop down lists.
Each case below shows the change in "No. of Investments" and then looks at the drop down list of "No. of Partners" and hides the rows accordingly (well, it's supposed to atleast).
Often, drop down list "No. of Partners" will not be engaged and will remain on "Please Select", so the first line of code will be used depending on the "No. of Investments" selected.
Currently the error being returned is "Compile Error - Argument no Optional" and it highlights the word "Union" on the first "Set R" line.
Drop down lists look like this;
No._Investments = Please Select,0,2,4,6,8,10
No._Partners = Please Select,2,3,4,5,6,7,8,9,10
Thanks for looking.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
Dim R As Range
Dim InvSel As Integer
Dim PartSel As Integer
Dim InvestRng As Range
Dim PartnerRng As Range
Set InvestRng = Range("No._Investments")
Set PartnerRng = Range("No._Partners")
Set i = Intersect(Target, InvestRng)
If Not i Is Nothing Then
InvSel = InvestRng.Value
PartSel = PartnerRng.Value
Select Case InvSel
Case "Please Select"
If PartSel = "Please Select" Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 2 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 3 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 4 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 5 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 6 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 7 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 8 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 9 Then
Set R = Union(Range("163:292"))
End If
Case 0
If PartSel = "Please Select" Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 2 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 3 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 4 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 5 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 6 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 7 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 8 Then
Set R = Union(Range("163:292"))
ElseIf PartSel = 9 Then
Set R = Union(Range("163:292"))
End If
Case 2
If PartSel = "Please Select" Then
Set R = Union(Range("165:173"), Range("178:186"))
ElseIf PartSel = 2 Then
Set R = Union(Range("166:173"), Range("179:186"))
ElseIf PartSel = 3 Then
Set R = Union(Range("167:173"), Range("180:186"))
ElseIf PartSel = 4 Then
Set R = Union(Range("168:173"), Range("181:186"))
ElseIf PartSel = 5 Then
Set R = Union(Range("169:173"), Range("182:186"))
ElseIf PartSel = 6 Then
Set R = Union(Range("170:173"), Range("183:186"))
ElseIf PartSel = 7 Then
Set R = Union(Range("171:173"), Range("184:186"))
ElseIf PartSel = 8 Then
Set R = Union(Range("172:173"), Range("185:186"))
ElseIf PartSel = 9 Then
Set R = Union(Range("173"), Range("186"))
End If
Case 4
If PartSel = "Please Select" Then
Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"))
ElseIf PartSel = 2 Then
Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))
ElseIf PartSel = 3 Then
Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))
ElseIf PartSel = 4 Then
Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))
ElseIf PartSel = 5 Then
Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"))
ElseIf PartSel = 6 Then
Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"))
ElseIf PartSel = 7 Then
Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"))
ElseIf PartSel = 8 Then
Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"))
ElseIf PartSel = 9 Then
Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"))
End If
Case 6
If PartSel = "Please Select" Then
Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"))
ElseIf PartSel = 2 Then
Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"))
ElseIf PartSel = 3 Then
Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"))
ElseIf PartSel = 4 Then
Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"))
ElseIf PartSel = 5 Then
Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"))
ElseIf PartSel = 6 Then
Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"))
ElseIf PartSel = 7 Then
Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"))
ElseIf PartSel = 8 Then
Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"))
ElseIf PartSel = 9 Then
Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"))
End If
Case 8
If PartSel = "Please Select" Then
Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"))
ElseIf PartSel = 2 Then
Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"))
ElseIf PartSel = 3 Then
Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"), Range("245:251"), Range("258:264"))
ElseIf PartSel = 4 Then
Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"), Range("246:251"), Range("259:264"))
ElseIf PartSel = 5 Then
Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"), Range("247:251"), Range("260:264"))
ElseIf PartSel = 6 Then
Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"), Range("248:251"), Range("261:264"))
ElseIf PartSel = 7 Then
Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"), Range("249:251"), Range("262:264"))
ElseIf PartSel = 8 Then
Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"), Range("250:251"), Range("263:264"))
ElseIf PartSel = 9 Then
Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"))
End If
Case 10
If PartSel = "Please Select" Then
Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"), Range("269:277"), Range("282:290"))
ElseIf PartSel = 2 Then
Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"), Range("270:277"), Range("283:290"))
ElseIf PartSel = 3 Then
Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("231:238"), Range("245:251"), Range("258:264"), Range("271:277"), Range("284:290"))
ElseIf PartSel = 4 Then
Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("231:238"), Range("246:251"), Range("259:264"), Range("272:277"), Range("285:290"))
ElseIf PartSel = 5 Then
Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("231:238"), Range("247:251"), Range("260:264"), Range("273:277"), Range("286:290"))
ElseIf PartSel = 6 Then
Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("231:238"), Range("248:251"), Range("261:264"), Range("274:277"), Range("287:290"))
ElseIf PartSel = 7 Then
Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("231:238"), Range("249:251"), Range("262:264"), Range("275:277"), Range("288:290"))
ElseIf PartSel = 8 Then
Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("231:238"), Range("250:251"), Range("263:264"), Range("276:277"), Range("289:290"))
ElseIf PartSel = 9 Then
Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"), Range("277"), Range("290"))
End If
End Select
If Not R Is Nothing Then R.EntireRow.Hidden = True
End If
If [h7] = "YES" Then
Sheets("K1a").Visible = True
Else
Sheets("K1a").Visible = False
End If
End Sub