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

Cliffork

New Member
Joined
Feb 12, 2020
Messages
18
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Upvote 0

Cliffork

New Member
Joined
Feb 12, 2020
Messages
18
Office Version
  1. 365
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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
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,186,848
Messages
5,960,185
Members
438,465
Latest member
wo_ofberry

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
Top