how to add next vba code to this one?

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
I am new to vba. My previous two vba codes are working.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String, Newvalue As String
  
    'On Error GoTo Exitsub
    Application.ScreenUpdating = False
    If Target.Address = "$B$9" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
            Else
                Target.Value = Oldvalue & ", " & Newvalue
            End If
        End If
        Application.EnableEvents = True
    Else
        Rows("40:89").EntireRow.Hidden = False
        x = Range("B31").Value
        Select Case x
            Case ""
                Rows("40:89").EntireRow.Hidden = True
            Case 1 To 49
                Rows(40 + x & ":89").EntireRow.Hidden = True
            Case 50
    End Select
    End If
    Application.ScreenUpdating = True
End Sub

I am trying to add this few lines to the current one
VBA Code:
y = Range("B5").Value
Select Case y
Case "OC FZ": Rows("12:93").EntireRow.Hidden = True
Case "FET": Rows("27:88").EntireRow.Hidden = True
End Select

Application.ScreenUpdating = True
End Sub


can anyone please help ? Thank you in advance
 
Last edited by a moderator:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
429
Office Version
  1. 365
Platform
  1. Windows
It solely depends on what you want to happen. You have a number of conditions in this code, and where and how this additional code should be implemented will depend entirely on which condition should priority over which other condition.

As it reads, this code is triggered if there is a change to the content of any of the cells on the worksheet. There are two top-level possibilities - either the change was made to: (1) Cell B9; or (2) any other cell. I suspect that you want this code under the second head - which focuses on hiding/unhiding certains rows - principally rows 40-89 - depending on what is in cell B31. Your new code purports to hide/unhide rows: (a) 19 to 93; or (b) 27 to 88 - depending on cell B5.

But the question then becomes - what should happen if there are values in cells B5 and B31 which overlap or conflict. So, what do you want to happen -for example - if B31 has the value 30 (resulting in rows 40:69 being visible and rows 70:89 being hidden) and cell B5 has FET (resulting in rows 27:88 being hidden)? If B31 takes priority, then rows 12 to 89 will be hidden except for rows 40 to 69, which will be visible. If B5 takes priority, then rows 27 to 88 (and potentially 89?) will be hidden.

There's a few other possible permutations, but I won't waste your time listing them all - it's probably a very easy adjustment to make, but we just need a bit more information.
 

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
Thank you for your reply. I have figured out a way where the values will not overlap.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String, Newvalue As String

'On Error GoTo Exitsub
Application.ScreenUpdating = False
If Target.Address = "$B$9" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
Application.EnableEvents = True
Exit Sub
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
Application.EnableEvents = True
Else
Rows("11:112").EntireRow.Hidden = False
x = Range("I31").Value

Select Case x
Case "FET"
Rows("11:91").EntireRow.Hidden = True
Case "OC FZ"
Rows("11:28").EntireRow.Hidden = True
Rows("33:95").EntireRow.Hidden = True
Case ""
Rows("40:89").EntireRow.Hidden = True
Case 1 To 49
Rows(40 + x & ":89").EntireRow.Hidden = True
Case 50

End Select
End If
Application.ScreenUpdating = True
End Sub

But i need another value y.
y = Range("B5").Value

Select Case y
Case "OC THW"
Rows("11:28").EntireRow.Hidden = True

Is there a way to add these few lines to the existing vba code?
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
429
Office Version
  1. 365
Platform
  1. Windows
I'm still not clear on what you're actually doing or what you actually want, but purely as a shot-in-the dark, try the following:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String, Newvalue As String

'On Error GoTo Exitsub
Application.ScreenUpdating = False

If Target.address = "$B$9" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
        Application.EnableEvents = True
        Exit Sub
    Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
    Application.EnableEvents = True
Else
    Rows("11:112").EntireRow.Hidden = False
    x = Range("I31").Value
    Select Case x
        Case "FET"
            Rows("11:91").EntireRow.Hidden = True
        Case "OC FZ"
            Rows("11:28").EntireRow.Hidden = True
            Rows("33:95").EntireRow.Hidden = True
        Case ""
            Rows("40:89").EntireRow.Hidden = True
        Case 1 To 49
            Rows(40 + x & ":89").EntireRow.Hidden = True
        Case 50
    End Select
    y = Range("B5").Value
    If y = "OC THW" Then Rows("11:28").EntireRow.Hidden = True
End If

Application.ScreenUpdating = True

End Sub
 
Solution

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
thank you so much. This is exactly the solution I am looking for. Sorry for not able to express what I want well.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,932
Messages
5,639,067
Members
417,067
Latest member
rohitbabshet

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