Sleeper: Showing specific range when the specific value chosen

[Nova]

Hello everyone!

I'm having a problem putting together the right code for my macro. I'm trying to create a code that would hide specific range every time I choose specific org in the in the range "C3". I have 3 choices identified by c3. At the very beginning of the run I need to unhide all the choices. In order to hide portion of them after all

I hope it's understandable.

I'm sure there is a better way to do it. :dunno

I appreciate your help.


Sub Macro1()
Sheets(CurrentSheet).Select
Rows("24:48").Select
Range("B24").Activate
Selection.EntireRow.Hidden = False
CAse 1
CurrentSheet = ActiveSheet.Name
If Range("C3").Value = "8880015" Then
Rows("33:47").Select
Range("B33").Activate
Selection.EntireRow.Hidden = True
Case 2
If Range("C3").Value = "88800002" Then
Rows("25:39").Select
Range("B25").Activate
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = False
Sheets("NON II (2)").Select
CurrentSheet = ActiveSheet.Name
STS = EssVRetrieve32(CurrentSheet, Range("RetrieveExP"), 1)
Cells.Select
Call Replace_NoData
End if
End Sub

[Zack Barresse]

Hi, a few questions. Can you post the routine Replace_NoData? And what does C3 hold? Data Validation? Are the values user entered? Can you explain the user proccess (exact steps a user would take and the results that should come from each)? Also, what is EssVRetrieve32?

[Nova]

What I'm trying to do is to show a specific area on the spreadsheet every time I pick a specific category in "C3" (Input cell). For example, I have a specific organization "org1" appearing in "c3" (chosen by me) that will hide certain area on the spreadsheet and show only the area I want to show for that "org1". The area showing is going to be populated with statistic related to this specific org.

the values are user entered.

EssVRetrieve32 - just a portion of the code ( code that retrieves data from the system)

See the zip file for more details.

[Zack Barresse]

Try using a worksheet change event.

Put this code into the worksheet module of the desired sheet ...


Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$3" Then Exit Sub 'not C3
If Selection.Count > 1 Then Exit Sub 'multiple selections
Select Case Target.Value
Case "org 1"
Me.Cells.EntireRow.Hidden = False
Me.Rows("24:48").Hidden = True
Case "8880015"
Me.Cells.EntireRow.Hidden = False
Me.Rows("33:47").Hidden = True
Case "88800002"
Me.Cells.EntireRow.Hidden = False
Me.Rows("25:39").Hidden = True
Case Else
Me.Cells.EntireRow.Hidden = False
End Select
End Sub

And I'm not sure what you're trying to do with the routine you posted, but you could maybe shorten it to ..


Option Explicit

Sub Macro1()
Dim CurrentSheet As String
CurrentSheet = ActiveSheet.Name
Sheets("NON II (2)").Select
STS = EssVRetrieve32(CurrentSheet, Range("RetrieveExP"), 1)
Cells.Select
Call Replace_NoData
Sheets(CurrentSheet).Select
End Sub

I'm not sure how needed the Select's really are, but if you can get rid of them, I'd do it. You'll just slow down your code if you don't.


HTH

[Zack Barresse]

Okay, saw your sheet upload after the last post. Try this ...


Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$8" Then Exit Sub 'not C3
If Selection.Count > 1 Then Exit Sub 'multiple selections
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Target.Value
Case "999900085"
Me.Cells.EntireRow.Hidden = False
Me.Rows("32:49").EntireRow.Hidden = True
Case "999900328"
Me.Cells.EntireRow.Hidden = False
Me.Rows("25:31").EntireRow.Hidden = True
Me.Rows("41:49").EntireRow.Hidden = True
Case "999900324"
Me.Cells.EntireRow.Hidden = False
Case Else
Me.Cells.EntireRow.Hidden = False
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Nova]

Can I incorporate your code into my original code? I've tried and it doesn't work.

I feel stupid, very stupid... ;)


Private Sub CommandButton1_Click()
Sheets("NON II (2)").Select
CurrentSheet = ActiveSheet.Name
STS = EssVRetrieve32(CurrentSheet, Range("RetrieveExP"), 1)
Cells.Select
Call Replace_NoData
Sheets(CurrentSheet).Select
End Sub

[Zack Barresse]

Well, some questions/issues:

1) I don't know what your CommandButton1 is. ActiveX? Userform control?
2) What is Replace_NoData?
3) You want to incorporate the worksheet_change event into this routine?

[Nova]

1)CommandButton1 is Userform control

2)Replace_NoData is just a part of the code to change setting after retrieving data from the system

3) Yes :)

[Zack Barresse]

Take the code out of the worksheet module. Set a new "Dim" something like this ...


Dim Target As Range, Sh As Worksheet
Set Target = Selection
Set Sh = Worksheet(Target.Parent.Name)

Then for every instance of "Me" replace it with "Sh".
I'll assume for now that you named your routine as "MyMacro", change if desired.

Still not sure what the usrform control (CommandButton) is actually supposed to do. So a very wild guess might be ...


Private Sub CommandButton1_Click()
Sheets("NON II (2)").Select
CurrentSheet = ActiveSheet.Name
STS = EssVRetrieve32(CurrentSheet, Range("RetrieveExP"), 1)
Cells.Select
Call Replace_NoData
Sheets(CurrentSheet).Select
Call MyMacro '<<-- Put this where you want
End Sub


Does this help?