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.

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?