How to get referenced cell to be recognized by macro

[kspeese]

I'm not sure what I'm even using is a macro, but I got it to kind of work. If a cell N1 on the worksheet contains a variable number 1-50 (signifies number of people), it will figure out what cells to unhide (will print like 9 people per page). I got it working correctly, the problem is when the cell that is being referenced (the one with the variable number) pulls the number from another worksheet (e.g. ='DATA SHEET'!I54), it does not update like when I manually type a number into that cell.

Here's the formula I'm using to display the cells I want:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("N1")) Is Nothing Then

Rows("3:252").Hidden = True

Select Case Target.Value

Case Is <= 9

Rows("3:47").Hidden = False 'Page 1

Case Is <= 18

Rows("3:92").Hidden = False 'Page 2

Case Is <= 27

Rows("3:137").Hidden = False 'Page 3

Case Is <= 36

Rows("3:182").Hidden = False 'Page 4

Case Is <= 45

Rows("3:227").Hidden = False 'Page 5

Case Is > 45

Rows("3:252").Hidden = False 'Page 6

Case Else

Rows("3:252").Hidden = True

End Select

End If

End Sub

Can anyone tell me how to get this to work right?

[abousetta]

What is the name of the sheet that contains this code, what is the name of the sheet that gets rows hidden and what is the name of the sheet that contains the cell that will contain the information used in the Selection process?

abousetta

[kspeese]

The name of the sheet that contains the code and has the rows hidden is "IJ1"
The name of the sheet that contains the data is "DATA SHEET"

Cell in N1 on Sheet IJI pulls data from cell 'DATA SHEET'!I54

Reading this, it's a "change event" where my manual input does what I want it to do, but whenever the value of any cell on the worksheet is changed by the result of a formula calculation, it doesn't work.

So, hoping that someone has some kind of work around like select the cell, then hit enter whenever the tab is selected. (No data needs to be input or manipulated on this worksheet.

Thanks

[abousetta]

Try this (untested):

Put this code in Sheets("DATA SHEET")

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim sht as worksheet
  If Not Intersect(Target, Sheets("DATA SHEET").Range("N1")) Is Nothing Then
    With Sheets("IJ1")
      .Rows("3:252").Hidden = True
      Select Case Target.Value
        Case > 45:  .Rows("3:252").Hidden = False 'Page 6
        Case <= 45: .Rows("3:227").Hidden = False 'Page 5
        Case <= 36: .Rows("3:182").Hidden = False 'Page 4
        Case <= 27: .Rows("3:137").Hidden = False 'Page 3
        Case <= 18: .Rows("3:92").Hidden = False  'Page 2
        Case <= 9:  .Rows("3:47").Hidden = False  'Page 1
      End Select
    End with  
  End if
End Sub

abousetta

[kspeese]

Still doesn't seem to work. The variable number is not something manually input anywhere, it's a the result of a formula. The data sheet takes information from other sheets and compiles it onto that sheet. Then the IJ1 sheet (and IJ2 sheet), pull from that sheet. I haven't messed around with pivot tables much, but there was an auto-update of the pivot cache which would refresh that data when clicking on the tab. I read somewhere about adding a calculate function that may work, but couldn't find any examples. I wonder if having it calculate the the original number +0 would work?

[millz]

It should be

If Not Intersect(Target, Sheets("DATA SHEET").Range("I54")) Is Nothing Then

[kspeese]

Still not working. It only hides the rows when I manually change the numbers on the IJ1 page and tab out or hit enter.

[abousetta]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[millz]

It should work if you are changing the value in Sheets("DATA SHEET").Range("I54").

Is this cell containing a formula as well?

If so, what data, and in where, are you changing? Is the change manual or with a macro?

The solution lies in putting the code into where the source data is updated/changed.

[kspeese]

I54 is a formula too. It's =COUNTIF($A$2:$A$51,I2)
A2:A51 is column that has Text - (A Person's Names). The names (Think Boss name, and the columns to the right are subordinate info) are pulled from 2 other sheets (Up to 25 Names Per Sheet - total of 50 rows). So the sum of the value of each name determines how many pages to show. If the Boss' Name has 45 people under him/her, I'd need like 5 or six pages to show.

I'm pretty sure I could do what I want with a Macro, but I was hoping to to just make it seamless. The only thing the end user should be typing in is the boss name, employee name and a few other details (Start time, Floor #, etc.). Another employee would go to the excel file, click on the IJ1 tab (or whatever I eventually name it) and see only the needed rows that contain data.

[millz]

Try amending abousetta's code. This should fire the change event as the Boss' names are being entered.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim sht as worksheet
  If Not Intersect(Target, Sheets("DATA SHEET").Range("A2:A51")) Is Nothing Then
    With Sheets("IJ1")
      .Rows("3:252").Hidden = True
      Select Case .Range("N1").Value
        Case > 45:  .Rows("3:252").Hidden = False 'Page 6
        Case <= 45: .Rows("3:227").Hidden = False 'Page 5
        Case <= 36: .Rows("3:182").Hidden = False 'Page 4
        Case <= 27: .Rows("3:137").Hidden = False 'Page 3
        Case <= 18: .Rows("3:92").Hidden = False  'Page 2
        Case <= 9:  .Rows("3:47").Hidden = False  'Page 1
      End Select
    End with  
  End if
End Sub

[kspeese]

Still not having much luck. I'll try to break it down and upload a sample.

Thanks all for trying.

[kspeese]

Okay, here's the workbook.

Data is manually entered in sheets "List One" and "List Two."

The data is pulled onto the data sheet and sorted.

IJ1 or IJ2 (I'm really only accounting for 2 bosses even though the Data Sheet sorts up to 3) is then accessed by an employee and prints out the worksheet. Rows corresponding to page numbers should be hidden based on the the numbers that have already been listed. I had a change code on each sheet that would do what I wanted if I manually typed the number into cells N2 on IJ1 and IJ2, but that code has been removed and instead replaced with what was provided on the data sheet.

I'm thinking the code needs to be on Sheets List One, and List Two since that's where the info is manually being typed, just not sure how to modify it. Then, would say Sheet IJ2 need to look at a different cell reference to not be confused with the number on the other sheet?

[abousetta]

This is why I don't like change events...

In DATA SHEET, put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim sht As Worksheet, Rng As Range
  Set Rng = Sheets("DATA SHEET").Range("I54")
  If Not Intersect(Target, Rng) Is Nothing Then
    With Sheets("IJ1")
      .Rows("3:252").Hidden = True
      If Rng.Value <= 9 Then
          .Rows("3:47").Hidden = False          'Page 1
      ElseIf Rng.Value <= 18 Then
          .Rows("3:92").Hidden = False          'Page 2
      ElseIf Rng.Value <= 27 Then
          .Rows("3:137").Hidden = False         'Page 3
      ElseIf Rng.Value <= 36 Then
          .Rows("3:182").Hidden = False         'Page 4
      ElseIf Rng.Value <= 45 Then
          .Rows("3:227").Hidden = False         'Page 5
      ElseIf Rng.Value > 45 Then
          .Rows("3:252").Hidden = False         'Page 6
      End If
    End With
  End If
End Sub

It turns out that Worksheet_Change is only triggered if the worksheet is the active sheet (so can't use similar code in the "IJ1" sheet). Also Select/ Case/ End Select keeps checking all the possible cases even if finds the one meeting it's needs. Therefore the number 1 is less than 9, 18, 27, etc. and so all the rows become visible. I changed it to IF/ ElseIF to get around this issue.

Let me know if you have any further problems.

abousetta

[kspeese]

Tried to incorporate the before mentioned with no luck. However, I did get this work-around to have it do what I want. I made a marco that simply copy and pastes (value) to the cell I needed the number in. I made cell M2 pull the number from the data sheet using. Since M2 it is a formula and doesn't affect change events, I just had the macro auto-run when the sheet was select which pasted the value to cell N2 (the reference cell). That seems to trigger the change event and the rows are auto adjusted.

Private Sub Worksheet_Activate()
'
' IJ1PASTEVAL Macro
'

'
    Range("M2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1").Select
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("N1")) Is Nothing Then

Rows("3:252").Hidden = True

Select Case Target.Value

Case Is <= 9

Rows("3:47").Hidden = False 'Page 1

Case Is <= 18

Rows("3:92").Hidden = False 'Page 2

Case Is <= 27

Rows("3:137").Hidden = False 'Page 3

Case Is <= 36

Rows("3:182").Hidden = False 'Page 4

Case Is <= 45

Rows("3:227").Hidden = False 'Page 5

Case Is > 45

Rows("3:252").Hidden = False 'Page 6

Case Else

Rows("3:252").Hidden = True

End Select

End If

End Sub

[kspeese]

Now that the sheet looks correct, is there a way to set the print area to correspond what is shown? Like if the number was 15 and it's only displaying 2 pages (Rows 1:92) (& Pages 3-6 are hidden), to only print the 2 pages if someone chose to select print?

Like: ElseIf Rng.Value <= 18 Then
.Rows("3:92").Hidden = False 'Page 2
ActiveSheet.PageSetup.PrintArea = "A1:J92"