Hiding a row in one sheet based on a cell in a different sheet?

Cliffork

New Member
Joined
Feb 12, 2020
Messages
21
Office Version
  1. 365
Hello,

I work in an electronics lab and manage a compilation file that we use for data entry whenever an instrument is returned for repair.
My first Sheet is called 'Customer Information', where we fill out all contact information and data about their RMA.
In cell B18 I have a drop down list to enter, what kind, if any, of handheld device was sent in alone with their instruments.
In another sheet titled 'Final Inspection', row 65 asks what software version they are running.
My final goal is to make it so that if "na" or "no" is entered in cell B18 on 'Customer Information' , that row 65 on 'Final Inspection' is hidden since it is unnecessary.

I have a similar hidden row in 'Customer Information' that hides row 17 if row 16 is a no, and it seems to work since it is on the same sheet. I tried making another cell in row 65 equal the value of B18, and use the same If/Then formula, but it doesn't seem to work. I've tried a few others I found on these forums but I can't seem to get them to work. Could anyone give me a hand? What I'm currently using is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G65").Value = "na" Then
Rows("65").EntireRow.Hidden = True
Else: Rows("65").EntireRow.Hidden = False
End If
End Sub


I also tried to use the one below to some success, but only when the data was entered directly into cell G65. If G65 uses formula ='Customer Information'!B18 the macro no longer functions properly.

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Final Inspection")
Select Case Target.Address(False, False)
Case "Customer information(B18)"
.Rows(65).Rows.Hidden = Target.Value = "na"
.Rows(65).Rows.Hidden = Target.Value = "no"
End Select
End With
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B18" Then
      With Sheets("Final Inspection")
         If Target.Value = "na" Or Target.Value = "no" Then
            .Rows(65).Hidden = True
         Else
            .Rows(65).Hidden = False
         End If
      End With
   End If
End Sub
This needs to go in the "Customer information" sheet module
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You're welcome & thanks for the feedback
I was wondering if you could help me again with another, very similar solution. I am basically trying to do the same thing again for another cell, except if the target cell contains the letter A or T. We have two different kinds of units that have serial numbers starting with either an A or T, and if this box is filled in at all I need to unhide a cell. Here's what I currently tried based on your previous help.

If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "B16" Then
With Sheets("Pre-Inspection")
If Target.value = "T" Or Target.Value = "A" Then
.Rows(58).Hidden = True
Else
.Rows(58).Hidden = False
End If
End With
End If

I don't know if maybe there is a different target.____ other than value that I could use instead possibly?
 
Upvote 0
If the cell value starts with A or T use
VBA Code:
If Target.value Like "T*" Or Target.Value Like "A*" Then
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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