Macro isn't running correctly - hide rows

F

fexcel

New Member
Joined
Dec 9, 2016
Messages
1
Hello guys
I hope your doing well.

I#m currently working on an excel file including 3 sheets.
The first one includes Datas.
The second one is called input and is a kind of form where 12 lists are placed. The user has the option to select between the different options.
For example one Option is "American or European observation".
Choosing for example "European" leads to hidden rows in the Output sheet.

My Problem is: choosing for example "European" in field 5. would hide the rows correctly. Jumping from field 5 to field 6 where we choose for example "Physical" would hide specified rows but unhide the rows which should be hidden after choosing "European".

My code is right there:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$4" Then 'Hides Rows according to Private Placement and Public offer x
If Target.Value = "Private Placement" Then
Sheets("Output").Rows("28:29").EntireRow.Hidden = True
Else
Sheets("Output").Rows("28:29").EntireRow.Hidden = False
End If
End If

If Target.Address = "$F$9" Then 'Nominal/Unit x
If Target.Value = "Nominal" Then
Sheets("Output").Rows("42:43").EntireRow.Hidden = True
Else
Sheets("Output").Rows("42:43").EntireRow.Hidden = False
End If
End If


If Target.Address = "$F$4" Then 'Hides Rows regarding PP PO x
If Target.Value = "Private Placement" Then
Sheets("Output").Rows("39").EntireRow.Hidden = True
Else
Sheets("Output").Rows("39").EntireRow.Hidden = False
End If
End If

If Target.Address = "$F$4" Then 'Hides Rows regarding PP PO x
If Target.Value = "Private Placement" Then
Sheets("Output").Rows("41").EntireRow.Hidden = True
Else
Sheets("Output").Rows("41").EntireRow.Hidden = False
End If
End If

If Target.Address = "$F$14" Then 'Hides Index Disclaimer x
If Target.Value = "Nein" Then
Sheets("Output").Rows("334").EntireRow.Hidden = True
Else
Sheets("Output").Rows("334").EntireRow.Hidden = False
End If
End If

If Target.Address = "$F$7" Then 'Hides ETF Rows x
If Target.Value = "Share" Then
Sheets("Output").Rows("86:91").Hidden = True
Else
If Target.Value = "Basket of Shares" Then
Sheets("Output").Rows("86:91").Hidden = True
Else
If Target.Value = "Index" Then
Sheets("Output").Rows("86:91").Hidden = True
Else
If Target.Value = "Basket of Indices" Then
Sheets("Output").Rows("86:91").Hidden = True
Else
Sheets("Output").Rows("86:91").Hidden = False
End If
End If
End If
End If
End If

If Target.Address = "$F$8" Then 'Underlying numbers x
Sheets("Output").Rows("66:84").Hidden = False
Sheets("Output").Rows("181:200").Hidden = False
Sheets("Output").Rows("224:242").Hidden = False
If Target >= 1 And Target <= 19 Then
Sheets("Output").Rows(65 + Target & ":84").Hidden = True
Sheets("Output").Rows(180 + Target & ":200").Hidden = True
Sheets("Output").Rows(223 + Target & ":242").Hidden = True
End If
End If


If Target.Address = "$F$12" Then 'Number of Valuation Dates x
Sheets("Output").Rows("141:151").Hidden = False
Sheets("Output").Rows("96:106").Hidden = False
If Target >= 1 And Target <= 11 Then
Sheets("Output").Rows(140 + Target & ":151").Hidden = True
Sheets("Output").Rows(95 + Target & ":106").Hidden = True
End If
End If


If Target.Address = "$F$13" Then 'Currency Risk x
If Target.Value = "No" Then
Sheets("Output").Rows("258:261").Hidden = True
Else
Sheets("Output").Rows("258:261").Hidden = False
End If
End If


If Target.Address = "$F$8" Then 'Message box number of underlying and Underlying ERROR x
Select Case Range("F7")
Case "Share", "Index"
If Range("F8").Value <> 1 Then MsgBox "Numbers of Underlying and selection in 4 don't match!", vbExclamation, "ERROR"
Case "Basket of Shares", "Basket of Indices"
If Range("F8").Value <= 1 Then MsgBox "Numbers of Underlying and selection in 4 don't match!", vbExclamation, "ERORR"
End Select
End If

If Target.Address = "$F$10" Then 'Message Box Index Physical ERROR x
Select Case Range("F6")
Case "Bonus Capped Single Index", "Bonus Uncapped Single Index", "Bonus Capped Worst of Indices", "Bonus Uncapped Worst of Indices", "Phoenix Single Index", "Phoenix Yeti Single Index", "Worst of Indices Phoenix Yeti", "Worst of Indices Phoenix", "Fix Coupon Express Single Index", "Coupon Linker Index", "Capital guaranteed Lookback Index", "Fix Coupon Express Worst of Indices", "Reverse Convertible Single Index", "Reverse Convertible Worst of Indices", "Autocall Single Index", "Autocall Worst of Indices"
If Range("F10").Value = "Physical" Then MsgBox "The Combination of Index and Physical is not possible!" & vbCrLf & "Please choose Cash for Index", vbExclamation, "ERROR"
End Select
End If

If Target.Address = "$F$10" Then 'Message Box Index, Basket of Indices and Physical ERROR x
Select Case Range("F7")
Case "Index", "Basket of Indices"
If Range("F10").Value = "Physical" Then MsgBox "The Combination of Index and Physical is not possible!" & vbCrLf & "Please choose Cash for Index", vbExclamation, "ERROR"
End Select
End If

If Target.Address = "$F$7" Then
Select Case Range("F6")
Case "Bonus Capped Worst of Indices", "Bonus Uncapped Worst of Indices", "Autocall Worst of Indices", "Fix Coupon Express Worst of Indices", "Worst of Indices Phoenix", "Worst of Indices Phoenix Yeti", "Worst of Indices Reverse Convertible"
If Range("F7").Value <> "Basket of Indices" Then MsgBox "The selected Payoff doesn't match with the Underlying in 4", vbExclamation, "ERROR"
Case "Bonus Capped Worst of Shares", "Bonus Uncapped Worst of Shares", "Autocall Worst of Shares", "Fix Coupon Express Worst of Shares", "Worst of Shares Phoenix", "Worst of Shares Phoenix Yeti", "Worst of Shares Reverse Convertible"
If Range("F7").Value <> "Basket of Shares" Then MsgBox "The selected Payoff doesn't match with the Underlying in 4", vbExclamation, "ERROR"
Case "Bonus Capped Single Index", "Bonus Uncapped Single Index", "Phoenix Single Index", "Phoenix Yeti Single Index", "Reverse Convertible Single Index", "Fix Coupon Express Single Index", "Coupon Linker Index", "Autocall Single Index"
If Range("F7").Value <> "Index" Then MsgBox "The selected Payoff doesn't match with the Underlying in 4", vbExclamation, "ERROR"
Case "Bonus Capped Single Share", "Bonus Uncapped Single Share", "Phoenix Single Share", "Phoenix Yeti Single Share", "Reverse Convertible Single Share", "Fix Coupon Express Single Share", "Coupon Linker Share", "Autocall Single Share"
If Range("F7").Value <> "Share" Then MsgBox "The selected Payoff doesn't match with the Underlying in 4", vbExclamation, "ERROR"
End Select
End If


If Target.Address = "$F$11" Then 'Message Box American Observation with selected Payoff ERROR x
Select Case Range("F6")
Case "Worst of Shares Reverse Convertible", "Worst of Indices Reverse Convertible", "Reverse Convertible Single Index", "Reverse Convertible Single Share", "Coupon Linker Index", "Capital guaranteed Lookback Index", "Capital guaranteed Lookback Share"
If Range("F11").Value = "American" Then MsgBox "An american observation is not possible with the selected Payoff!" & vbCrLf & "Please use European observation", vbExclamation, "ERROR"
End Select
End If


With Sheets("Output") 'Hides Valuation Date Header x
Select Case Target.Value
Case "Index", "Basket of Indices"
Sheets("Output").Rows("139").Hidden = True
Case "Share", "Basket of Shares"
Sheets("Output").Rows("138").Hidden = True
Case Else
Sheets("Output").Rows("138").Hidden = False
Sheets("Output").Rows("139").Hidden = False
End Select
End With




With Sheets("Output") 'Hides Cash or Physical Rows x
Sheets("Output").Rows("246:253").Hidden = False
Sheets("Output").Rows("165").Hidden = False
Sheets("Output").Rows("176").Hidden = False
Select Case Target.Value
Case "Cash"
Sheets("Output").Rows("246:253").Hidden = True
Sheets("Output").Rows("165").Hidden = True
Sheets("Output").Rows("176").Hidden = True
Case "Physical"
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case Else
Sheets("Output").Rows("246:253").Hidden = False
Sheets("Output").Rows("165").Hidden = False
Sheets("Output").Rows("176").Hidden = False
Sheets("Output").Rows("162:164").Hidden = False
Sheets("Output").Rows("173:175").Hidden = False
End Select
End With

With Sheets("Output") 'American European Observation x
Sheets("Output").Rows("162:164").Hidden = False
Sheets("Output").Rows("173:175").Hidden = False
Sheets("Output").Rows("159:161").Hidden = False
Sheets("Output").Rows("170:172").Hidden = False
Select Case Target.Value
Case "European"
Sheets("Output").Rows("159:161").Hidden = True
Sheets("Output").Rows("170:172").Hidden = True
Sheets("Output").Rows("201:212").Hidden = True
Sheets("Output").Rows("177:200").Hidden = True
Case "American"
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case Else
Sheets("Output").Rows("162:164").Hidden = False
Sheets("Output").Rows("173:175").Hidden = False
Sheets("Output").Rows("159:161").Hidden = False
Sheets("Output").Rows("170:172").Hidden = False
End Select
End With


With Sheets("Output") 'Payoffs x
Sheets("Output").Rows("112:154").Hidden = False
Sheets("Output").Rows("254:257").Hidden = False
Sheets("Output").Rows("162:164").Hidden = False
Sheets("Output").Rows("173:175").Hidden = False
Select Case Target.Value
Case "Bonus Capped Single Index"
Sheets("Output").Rows("112:154").Hidden = True
Case "Bonus Capped Single Share"
Sheets("Output").Rows("112:154").Hidden = True
Case "Bonus Capped Worst of Indices"
Sheets("Output").Rows("112:154").Hidden = True
Case "Bonus Capped Worst of Shares"
Sheets("Output").Rows("112:154").Hidden = True
Case "Bonus Uncapped Single Share"
Sheets("Output").Rows("112:154").Hidden = True
Sheets("Output").Rows("256:257").Hidden = True
Case "Bonus Uncapped Single Index"
Sheets("Output").Rows("112:154").Hidden = True
Sheets("Output").Rows("256:257").Hidden = True
Case "Bonus Uncapped Worst of Shares"
Sheets("Output").Rows("112:154").Hidden = True
Sheets("Output").Rows("256:257").Hidden = True
Case "Bonus Uncapped Worst of Indices"
Sheets("Output").Rows("112:154").Hidden = True
Sheets("Output").Rows("256:257").Hidden = True
Case "Phoenix Single Index"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Phoenix Single Share"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Worst of Indices Phoenix"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Worst of Shares Phoenix"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Phoenix Yeti Single Share"
Sheets("Output").Rows("116:131").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Phoenix Yeti Single Index"
Sheets("Output").Rows("116:131").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Worst of Shares Phoenix Yeti"
Sheets("Output").Rows("116:131").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Worst of Indices Phoenix Yeti"
Sheets("Output").Rows("116:131").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Autocall Single Index"
Sheets("Output").Rows("112:137").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Autocall Single Share"
Sheets("Output").Rows("112:137").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Autocall Worst of Indices"
Sheets("Output").Rows("112:137").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Autocall Worst of Shares"
Sheets("Output").Rows("112:137").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Reverse Convertible Single Share"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("138:154").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case "Reverse Convertible Single Index"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("138:154").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case "Worst of Shares Reverse Convertible"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("138:154").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case "Worst of Indices Reverse Convertible"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("138:154").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Sheets("Output").Rows("162:164").Hidden = True
Sheets("Output").Rows("173:175").Hidden = True
Case "Fix Coupon Single Share"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Fix Coupon Single Index"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Fix Coupon Worst of Shares"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Fix Coupon Worst of Indices"
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Case "Coupon Linker Index"
Sheets("Output").Rows("160:164").Hidden = True
Sheets("Output").Rows("171:175").Hidden = True
Sheets("Output").Rows("254:257").Hidden = True
Sheets("Output").Rows("116:133").Hidden = True
Sheets("Output").Rows("136:152").Hidden = True
Case Else
Sheets("output").Rows("116:133").Hidden = False
Sheets("Output").Rows("112:154").Hidden = False
Sheets("Output").Rows("254:257").Hidden = False
Sheets("Output").Rows("162:164").Hidden = False
Sheets("Output").Rows("173:175").Hidden = False
End Select
End With
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
389
kevin9999
K
S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
749
dmt32
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
O
  • Question
VBA to append data into columns (only tables) - excluding a random cell
Replies
0
Views
1K
omgxdee
O
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top