PDA

View Full Version : [SOLVED] VB Code for EXCEL 2010 - PLEASE HELP!!



onetel_comms
02-26-2015, 06:47 PM
Hi All,

I am hoping some one can help. I am new to VB in excel and I have been tasked with creating a document for work purposes. The idea is to create a spread sheet with multiple drop down options which when selected hide certain rows.

I have successfully created the drop down boxes, with "YES", "NO" and "-" as the options however I keep running into issues when I try to program the VB side of things. The issue is that some boxes when selected do what they are suppose to do however other don't do anything. Its really strange to me as when I unhide the whole sheet and select each option individually it seems to work, but when I save the sheet and reopen it I get the same issue.

Can anyone help? I have listed the VB code below. Any help will be greatly appreciated.




Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Target.Parent.Range("B63")
Select Case Target.Address
Case "$B$63"
If Target.Value = "-" Then
Rows("64:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("64:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("64:78").Hidden = False
End If
End Select
Dim Rng2 As Range
Set Rng2 = Target.Parent.Range("B78")
Select Case Target.Address
Case "$B$78"
If Target.Value = "-" Then
Rows("79:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("79:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("79:127").Hidden = False
End If
End Select
Dim Rng3 As Range
Set Rng3 = Target.Parent.Range("B127")
Select Case Target.Address
Case "$B$127"
If Target.Value = "-" Then
Rows("128:307").Hidden = True
ElseIf Target.Value = "YES" Then
Rows("128:130").Hidden = False
Else
Rows("128:307").Hidden = True
MsgBox "Please DO NOT continue."
End If
End Select
Dim Rng4 As Range
Set Rng4 = Target.Parent.Range("B130")
Select Case Target.Address
Case "$B$130"
If Target.Value = "-" Then
Rows("131:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("131:143").Hidden = True
Rows("187:307").Hidden = True
Else
Rows("131:186").Hidden = False
MsgBox "Please check with support"
End If
End Select
Dim Rng5 As Range
Set Rng5 = Target.Parent.Range("B186")
Select Case Target.Address
Case "$B$186"

If Target.Value = "-" Then
Rows("187:307").Hidden = True

ElseIf Target.Value = "YES" Then
Rows("187:195").Hidden = True
Rows("207:227").Hidden = True
Rows("280:306").Hidden = True

Else
Rows("187:195").Hidden = False
Rows("207:227").Hidden = False
Rows("280:306").Hidden = False

End If
End Select
Dim Rng6 As Range
Set Rng6 = Target.Parent.Range("B195")
Select Case Target.Address
Case "$B$195"
If Target.Value = "-" Then
Rows("216:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("216:222").Hidden = True
Rows("293:306").Hidden = True
Else
Rows("216:222").Hidden = False
Rows("293:306").Hidden = False
End If
End Select
End Sub

HaHoBe
02-26-2015, 09:50 PM
posted as well here
http://www.mrexcel.com/forum/excel-questions/838878-vbulletin-code-excel-2010-please-help.html
http://www.excelforum.com/excel-programming-vba-macros/1069101-vb-code-for-excel-2010-please-help.html.

Holger

onetel_comms
02-27-2015, 01:08 AM
Hi, yes I have posted to a couple forums.. as I said I am completely new to the Excel and VB world so wasn't sure which forum would be able to help.

If if you can help, please let me know

Cheers

Yongle
02-27-2015, 01:45 AM
When you join any forum it helps you (and everyone in the forum) if you learn about how the forum works.
FAQ "How to use this site" - is very helpful and guides new members.
Everyone is happy to help - and the easier you make it for us, the more likely you are to get the help you want.

I notice that someone is already helping you on http://www.mrexcel.com/forum/excel-questions/838878-vbulletin-code-excel-2010-please-help.html and it looks as though you will get your answer there. So it would be good etiquette to go to thread tools and mark this thread as solved . By all means, start a new thread later if you do not get your complete answer, but by then, I think your questions will have changed.

thanks