Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 72

Thread: IF statment nested within VBA Code

  1. 09-08-2023, 06:04 AM #41
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    OK I'm trying a different approach here now because I cannot get this to work using both cell.

    I would prefer to have this working from both cells but it just seems to ignore whatever I select in C4's drop down list.

    My original idea was use the values from C3 to hide/unhide the majority of fields required for the mode selected and then when the user selects the actual lane from cell c4, I can further streamline the fields by removing and or adding back in the required fields.

    For example I don't need row 8 to be visible when JFK is selected as the end destination but it is required when LAX is selected.

    There are other optimisation (rows to hide/unhide) for other lanes in Cell C4 but again, if I can get one to work then I can repeat for the rest


    So a different approach is to just work from Cell C4 only, this is not as future proof for me as I would like and I would prefer to get the above working but it just seems to be eluding me currently and I'm nearly 5 day in on this and behind schedule.

    So another way to do this and hopefully simplify this, is to use the values from cell C4 only, as they are unique to each other so I can adjust my code to look only at C4 and then do its hidding/unhiding etc.
    I have tried to adjust as per below bu t it fails so need your help/guidance again please

    Ive insert the below under Air as acheck as these fieds are only needed from Ocean and so shouldn't be visible when selecting a Air route.

    Range("A23:A25").EntireRow.Hidden = False
    Private Sub Worksheet_Change(ByVal Target As Range)    Dim r As Range
    Set r = Target.Cells(1, 1)
    If r.Address <> "$C$4" Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub
    Application.EnableEvents = False
    Unprotect Password:="dlm"
Select Case r.Value
Case "Warsaw to JFK"
    Range("A5:A6").EntireRow.Hidden = True
    Range("A8:A10").EntireRow.Hidden = True
    Range("A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = False
Case "Warsaw to LAX"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A8:A11").EntireRow.Hidden = False
    Range("A12").EntireRow.Hidden = True
    Range("A17").EntireRow.Hidden = False
    Range("A20").EntireRow.Hidden = False
Case "Malpensa to JFK"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
Case "Malpensa to LAX"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to JFK"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to LAX"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
Case "Ocean_EU_to_US"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
Case "Overland"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
End Select
    ActiveSheet.Protect Password:="dlm"
    Application.EnableEvents = True
End Sub
    Reply With Quote Reply With Quote
  2. 09-08-2023, 06:16 AM #42
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Be glad to, but could you make a small workbook with the entire macro(s) and attach it?

    I'm not sure how the entire macro looks

    A more wordy way (not always a good thing) is to test for both C3 and C4 combined


    Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, rC3 As Range, rC4 As Range
    
    Set r = Target.Cells(1, 1)  '   only check first cell
    Set rC3 = Range("C3")
    Set rC4 = Range("C4")
    
    
    'personal style - I like to exit quick
    If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub
    
    Application.EnableEvents = False




    Unprotect Password:="dlm"
    
    Select Case rC3.Value & "#" & rC4.Value
        Case "Air" & "#" & "Warsaw to JFK"
            Range("A5:A6").EntireRow.Hidden = True
            Range("A8:A10").EntireRow.Hidden = True
            Range("A11").EntireRow.Hidden = False
            Range("A12:A21").EntireRow.Hidden = True
            Range("A23:A25").EntireRow.Hidden = False


        Case "Air" & "#" & "Warsaw to LAX"
            Range("A6").EntireRow.Hidden = True
            Range("A8").EntireRow.Hidden = False
            Range("A8:A11").EntireRow.Hidden = False
            Range("A12").EntireRow.Hidden = True
            Range("A17").EntireRow.Hidden = False
            Range("A20").EntireRow.Hidden = False
            
        'etc
        'etc
        
        Case Else
            If Len(rC3.Value) = 0 Then
                rC4.Value = "Select Destination"
            ElseIf Len(rC4.Value) = 0 Then
                rC3.Value = "Select Origin"
            Else
                MsgBox "Origin and/or Destinaation not programed for"
            End If
    End Select
    
    ActiveSheet.Protect Password:="dlm"
    Application.EnableEvents = True
End Sub
    Last edited by Paul_Hossler; 09-08-2023 at 06:38 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  3. 09-08-2023, 06:33 AM #43
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    sure but not sure it will work in a cut down version so please find attached workbook

    regards

    Richard
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  4. 09-08-2023, 06:33 AM #44
    georgiboy
    georgiboy is offline
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,198
    Location
    So a different approach is to just work from Cell C4 only, this is not as future proof for me as I would like and I would prefer to get the above working but it just seems to be eluding me currently and I'm nearly 5 day in on this and behind schedule.
    I think this was suggested in post 25
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2403, Build 17425.20146
    Reply With Quote Reply With Quote
  5. 09-08-2023, 06:45 AM #45
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    It was yes but as outlined in my last message the preferred method is to combine both, this future proofs me better when I need to make additions to the lanes and or modes.

    The only reasons working from one works is because the names are unique in C4 but they may not be in the future hence why 2 is better than one.
    Reply With Quote Reply With Quote
  6. 09-08-2023, 07:11 AM #46
    georgiboy
    georgiboy is offline
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,198
    Location
    Sorry I should have said post 25 does both C3 & C4, post 19 works from C4 only.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2403, Build 17425.20146
    Reply With Quote Reply With Quote
  7. 09-08-2023, 03:04 PM #47
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Some things to think about

    Not all C4 choices apply to some C3 selections so you might need to make a matrix


    Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, rC3 As Range, rC4 As Range
    
    Set r = Target.Cells(1, 1)
    Set rC3 = Range("C3")
    Set rC4 = Range("C4")
    
    If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub
    
    If Len(rC3.Value) = 0 Or Len(rC4.Value) = 0 Then Exit Sub

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Unprotect Password:="dlm"
    
    'show them all to start and then hide the ones based on C3 and/or C4
    Rows("1:86").Hidden = False
    
    Select Case rC3.Value
        Case "Air"
            'if you always need to hide rows for Air


        Case "Ocean_EU_to_US"
            'if you need to hide rows
        
        Case "Ocean_Asia_to_EU"
            'if you need to hide rows
        
        
        Case "Overland"
            'if you need to hide rows
        
    End Select
    
    
    'depending on C3, not all of these apply
    Select Case rC4.Value
        Case "Warsaw to JFK"
            Range("A5:A6").EntireRow.Hidden = True
            Range("A8:A10").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
        Case "Warsaw to LAX"
            Range("A6").EntireRow.Hidden = True
            Range("A12").EntireRow.Hidden = True
        Case "Malpensa to JFK"
            Range("A6").EntireRow.Hidden = True
            Range("A8").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
        Case "Malpensa to LAX"
            Range("A6").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
        Case "Heathrow to JFK"
            Range("A6").EntireRow.Hidden = True
            Range("A8").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
        Case "Heathrow to LAX"
            Range("A6").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
        Case "Ocean_EU_to_US"
            etc.
        Case "Ocean_Asia_to_EU"
            etc.
        Case "Overland"
            etc.
    End Select
    
'    ActiveSheet.Protect Password:="dlm" '   <<<<<<<<<<<<<<<<<<< for testing
    Application.EnableEvents = True
    Application.ScreenUpdating = true



End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  8. 09-12-2023, 07:43 AM #48
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    This worked a treat, I can't thank you enough

    I've added an extra line to make the Rate Calc sheet look cleaner until the lane has been selected, it basically stop/hides the 'False' being shown because the user hasn't selected their lane yet from C4 cell..

    If Target.Address = "$C$3" Then
        Range("C4").Value = "Please Select Origin..."
    My only stumbling block now is I need to be able to hide an 'Active xControl check box' when a certain lane is selected , namely the below, it fails to hide or unhide the check boxes, any ideas?

    Case "Ocean_EU_to_US" & "#" & "Genoa to New York"    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.CheckBox1.Visible = True
    ActiveSheet.CheckBox2.Visible = True
    ActiveSheet.CheckBox3.Visible = True
    ActiveSheet.CheckBox4.Visible = True
    ActiveSheet.CheckBox5.Visible = True
    ActiveSheet.CheckBox6.Visible = True
            
Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.CheckBox1.Visible = False
    ActiveSheet.CheckBox2.Visible = False
    ActiveSheet.CheckBox3.Visible = False
    ActiveSheet.CheckBox4.Visible = False
    ActiveSheet.CheckBox5.Visible = False
    ActiveSheet.CheckBox6.Visible = False
    Full code is below

    Private Sub Worksheet_Change(ByVal Target As Range)    Dim r As Range, rC3 As Range, rC4 As Range
    
    Set r = Target.Cells(1, 1)  'only check first cell
    Set rC3 = Range("C3")
    Set rC4 = Range("C4")
         
    If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub
If Target.Address = "$C$3" Then
        Range("C4").Value = "Please Select Origin..."
        End If
        
        Application.EnableEvents = False
    Unprotect Password:="dlm"
     
Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to New York"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    
Case "Air" & "#" & "Warsaw to Los Angeles"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
            
Case "Air" & "#" & "Malpensa to New York"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
        
Case "Air" & "#" & "Malpensa to Los Angeles"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    
Case "Air" & "#" & "Heathrow to New York"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    
Case "Air" & "#" & "Heathrow to Los Angeles"
    Range("A6").EntireRow.Hidden = True
    Range("A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    
Case "Ocean_EU_to_US" & "#" & "Genoa to New York"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.CheckBox1.Visible = True
    ActiveSheet.CheckBox2.Visible = True
    ActiveSheet.CheckBox3.Visible = True
    ActiveSheet.CheckBox4.Visible = True
    ActiveSheet.CheckBox5.Visible = True
    ActiveSheet.CheckBox6.Visible = True
            
Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.CheckBox1.Visible = False
    ActiveSheet.CheckBox2.Visible = False
    ActiveSheet.CheckBox3.Visible = False
    ActiveSheet.CheckBox4.Visible = False
    ActiveSheet.CheckBox5.Visible = False
    ActiveSheet.CheckBox6.Visible = False
             
Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to New York"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_EU_to_US" & "#" & "Hamburg to New York"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
                
Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
        
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to FXT/SOU"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to FXT/SOU"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
            
Case "Overland" & "#" & "PL to UK"
    Range("A8:A13").EntireRow.Hidden = True
    Range("A14").EntireRow.Hidden = False
    Range("A15:A21").EntireRow.Hidden = True
    Range("A38:A74").EntireRow.Hidden = True
    Range("A24:A25").EntireRow.Hidden = True
    
Case "Overland" & "#" & "UK to PL"
    Range("A8:A13").EntireRow.Hidden = True
    Range("A14").EntireRow.Hidden = False
    Range("A15:A21").EntireRow.Hidden = True
    Range("A38:A74").EntireRow.Hidden = True
    Range("A24:A25").EntireRow.Hidden = True
End Select
    
    ActiveSheet.Protect Password:="dlm"
    Application.EnableEvents = True
End Sub
    Reply With Quote Reply With Quote
  9. 09-12-2023, 12:05 PM #49
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Option Explicit


Sub cboxHide()


    ActiveSheet.Shapes("Checkbox1").Visible = False


End Sub


Sub cboxShow()


    ActiveSheet.Shapes("CheckBox1").Visible = True


End Sub
    But I usually rename them so that the code is more readable and I'm less likely to make errors
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  10. 09-13-2023, 01:59 AM #50
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    That makes sense to rename them, I've renamed them now for ease of reference.

    Your code is complete but I need to insert it into my code without breaking the rest/flow of the code, I have inserted as per below but nothing happened when I select 'Genoa to New York' or 'Genoa to Los Angeles'.

    Basically the check boxes are only needed for 'Genoa to New York' and then will be hidden for the rest, if I can get the below two to work correctly, I can then replicate for the other lanes.

    Case "Ocean_EU_to_US" & "#" & "Genoa to New York"    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.Shapes("RFR20").Visible = True
    ActiveSheet.Shapes("RFR40").Visible = True
    ActiveSheet.Shapes("RFR40HQ").Visible = True
    ActiveSheet.Shapes("Priority20").Visible = True
    ActiveSheet.Shapes("Priority40").Visible = True
    ActiveSheet.Shapes("Priority40HQ").Visible = True
    
Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
    Range("A51:A74").EntireRow.Hidden = False
    Range("A23:A25").EntireRow.Hidden = False
    Range("A54:A57").EntireRow.Hidden = True
    ActiveSheet.Shapes("RFR20").Visible = False
    ActiveSheet.Shapes("RFR40").Visible = False
    ActiveSheet.Shapes("RFR40HQ").Visible = False
    ActiveSheet.Shapes("Priority20").Visible = False
    ActiveSheet.Shapes("Priority40").Visible = False
    ActiveSheet.Shapes("Priority40HQ").Visible = False
    Last edited by Scuba; 09-13-2023 at 02:13 AM.
    Reply With Quote Reply With Quote
  11. 09-13-2023, 07:44 AM #51
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Without a sample workbook with the latest macros, it's hard to say

    Here's a snippet that "looks OK" to me

    I think it's easier to 'reset' to a visible configuration each time and then apply the specifics. So I like to unhide all rows and hide the checkboxes and the depending on the choices, hide N/A rows and show the checkboxes

    One the checkboxes are made visible, they stay visible until they get hidden and by always starting with a known configuration, I find it easier to manage

        Application.EnableEvents = False
    Unprotect Password:="dlm"
    
    Shapes("RFR20").Visible = False
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = False
    Shapes("Priority40").Visible = False
    Shapes("Priority40HQ").Visible = False
     
    Rows("1:86").Hidden = False
    
     
    Select Case rC3.Value & "#" & rC4.Value
        Case "Air" & "#" & "Warsaw to New York"
            Range("A6").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
            Range("A23:A25").EntireRow.Hidden = True
            
        Case "Air" & "#" & "Warsaw to Los Angeles"
            Range("A6").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
            Range("A23:A25").EntireRow.Hidden = True
                    
        Case "Air" & "#" & "Malpensa to New York"
            Range("A6").EntireRow.Hidden = True
            Range("A8").EntireRow.Hidden = True
            Range("A12:A21").EntireRow.Hidden = True
            Range("A23:A25").EntireRow.Hidden = True
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  12. 09-13-2023, 08:35 AM #52
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    I have uploaded the file for reference if that is easier, let me know please?

    Once again, thank you for all your help so far

    Regards

    Richard
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  13. 09-14-2023, 08:50 AM #53
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    I shared the workbook in post 52 as requested, I have reviewed your response in post 51 and I think I understand what you are saying, however the hiding and unhiding of rows and or check boxes is all dependent on what is selected by rC3 & rC4, mainly rC4, hence why I was trying to use each Case under Rc4 as the determining factor, for example

    if rC4 = 'Warsaw to New York' then

    Shapes("RFR20").Visible = True
    Shapes("RFR40").Visible = True
    Shapes("RFR40HQ").Visible = True
    Shapes("Priority20").Visible = True
    Shapes("Priority40").Visible = True
    Shapes("Priority40HQ").Visible = True
    but if rC4='Warsaw to Los Angeles' then

     
    Shapes("RFR20").Visible = False
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = True
    Shapes("Priority40").Visible = True
    Shapes("Priority40HQ").Visible = True

    Basically RFR means Reefer movements & Priority means Priority movements, we only move Reefer containers from 'Warsaw to New York', hence why they are required to be visible when this is selected in rC4 but hidden when rC4 is 'Warsaw to Los Angeles'

    This is slightly different to what I posted in 48 but was the true end game as once I got the logic or code to work, I could then change it to what I needed hidden/unhidden etc per rC4 selection.



    Regards


    Richard
    Reply With Quote Reply With Quote
  14. 09-14-2023, 10:23 AM #54
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Many ways to do it, but I think it's easier to follow to start off by hiding the check boxes and showing all the rows.

    Then depending on choices in C3 and C4 to hide the inapplicable rows depending on combanation ofC3+C4and showing the appropriate check boxes depending on C4

    That way you always start with a known status

    Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, rC3 As Range, rC4 As Range
    
    Set r = Target.Cells(1, 1)  'only check first cell
    Set rC3 = Range("C3")
    Set rC4 = Range("C4")
         
    If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub


    If Target.Address = "$C$3" Then
        Range("C4").Value = "Please Select Origin..."
    End If
        
    Application.EnableEvents = False
    Unprotect Password:="dlm"
     
  
    Shapes("RFR20").Visible = False
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = False
    Shapes("Priority40").Visible = False
    Shapes("Priority40HQ").Visible = False
     
    Rows("1:86").Hidden = False
    
    Select Case rC3.Value & "#" & rC4.Value
    Case "Air" & "#" & "Warsaw to New York"
        Range("A6").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
        
    Case "Air" & "#" & "Warsaw to Los Angeles"
        Range("A6").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
                
    Case "Air" & "#" & "Malpensa to New York"
        Range("A6").EntireRow.Hidden = True
        Range("A8").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
            
    Case "Air" & "#" & "Malpensa to Los Angeles"
        Range("A6").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
        
    Case "Air" & "#" & "Heathrow to New York"
        Range("A6").EntireRow.Hidden = True
        Range("A8").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
        
    Case "Air" & "#" & "Heathrow to Los Angeles"
        Range("A6").EntireRow.Hidden = True
        Range("A12:A21").EntireRow.Hidden = True
        Range("A23:A25").EntireRow.Hidden = True
        
    Case "Ocean_EU_to_US" & "#" & "Genoa to New York"
        Range("A54:A57").EntireRow.Hidden = True
        
    Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
        Range("A54:A57").EntireRow.Hidden = True
                         
    Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles"
        
    Case "Ocean_EU_to_US" & "#" & "Gdynia to New York"
        
    Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles"
        
    Case "Ocean_EU_to_US" & "#" & "Hamburg to New York"
        
    Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles"
                    
    Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York"
        
    Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles"
        
    Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa"
            
    Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa"
        
    Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk"
        
    Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk"
        
    Case "Ocean_Asia_to_EU" & "#" & "Shanghai to FXT/SOU"
        
    Case "Ocean_Asia_to_EU" & "#" & "Xiamen to FXT/SOU"
                
    Case "Overland" & "#" & "PL to UK", "Overland" & "#" & "UK to PL"
        Range("A8:A13").EntireRow.Hidden = True
        Range("A15:A21").EntireRow.Hidden = True
        Range("A38:A74").EntireRow.Hidden = True
        Range("A24:A25").EntireRow.Hidden = True
    End Select
    
    Select Case rC4.Value
        Case "Warsaw to New York"
            Shapes("RFR20").Visible = True
            Shapes("RFR40").Visible = True
            Shapes("RFR40HQ").Visible = True
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True


        Case "Warsaw to Los Angeles"
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
    End Select
    
    ActiveSheet.Protect Password:="dlm"
    Application.EnableEvents = True
End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  15. 09-15-2023, 02:47 AM #55
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location

    Thumbs up

    Hi Paul,

    I will most certainly try this approach yes but not sure how this will work because its not a simple on or off before the rC4 lanes are selected.

    Yes, the check boxes are required to be hidden for all 'Air' or 'Overland' lanes but then only 3 of them are required to be visible for all Ocean lanes and then for a specific lane 'Genoa to New York', all 6 are required.

    If we are turning them on or off in the first instance before rC4 is selected how will that work?

    Sorry for questioning you, I'm just simply trying to get my head around all this and of course learn as I go along
    regards

    Scrap the above Paul, this works a treat, perfect!
    I can't thank you enough and thank you or being patience with me, this is all new to me but I have learnt a lot all ready and will apply this to other projects in the future, once again many thanks Paul

    Regards

    Richard
    Last edited by Scuba; 09-15-2023 at 02:57 AM.
    Reply With Quote Reply With Quote
  16. 09-15-2023, 05:37 AM #56
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    You may be able to help me with the below code, it fails when run.

    Basically I'm hiding certain rows when an active check box is true and the value in C4 = Warsaw to New York

    This is additional to what you have helped me with previously.

    Sub BreakDownofCHarges()'Declare variables
Dim xCheckbox As Boolean
Dim cellC4 As String


'Get the value of the active x checkbox
xCheckbox = Sheets("Rate Calc").CheckBoxes("RevealBreakDown").Value


'Get the value of cell C4
cellC4 = Sheets("Rate Calc").Cells(4, 2).Value


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
If xCheckbox And cellC4 = "Warsaw to New York" Then
Sheets("Rate Calc").Rows(38:43).EntireRow.Hidden = True
Sheets("Rate Calc").Rows(52:58).EntireRow.Hidden = True
End If


End Sub
    Reply With Quote Reply With Quote
  17. 09-15-2023, 10:57 AM #57
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Option Explicit


Sub BreakDownofCHarges()


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
With Sheets("Rate Calc")
    If .CheckBoxes("RevealBreakDown") And .Cells(4, 2).Value = "Warsaw to New York" Then
        .Rows("38:43").Hidden = True        '   .Rows(..) with a more than one  requires a string, don't know why ... that's just MS
        .Rows("52:58").Hidden = True        '    Also .EntireRow is assumed
    End If
End With


End Sub

    Another way

    Sub BreakDownofCharges_1()


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
With Sheets("Rate Calc")
    If .CheckBoxes("RevealBreakDown") And .Cells(4, 2).Value = "Warsaw to New York" Then
        .Rows(38).Resize(6).Hidden = True
        .Rows(52).Resize(7).Hidden = True
    End If
End With


End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  18. 09-21-2023, 02:15 AM #58
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Worked a Dream Paul

    Thank you
    Reply With Quote Reply With Quote
  19. 09-21-2023, 02:26 AM #59
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    I realised after getting this to work perfectly with your expert help that I needed to add another clause or conditional row, so depending on what is selected in C5, different rows are hidden or unhidden.

    Basically if C5 = Factory Load then line 17 is visible but if its CFS Loading then line 17 is hidden and line 19 is visible, this is because we have different collection locations depending on Factory vs CFS loading, it changes slightly for the other different modes, some have it some don't, I've adjusted the code so the correct rows are showing hidden or unhidden.

    So building on what you had shown me already I adjusted the code accordingly but something is missing/not working on the below code as now when I change C4, nothing happens, especially if I am swapping between different modes/lanes (i.e. changing C3 and or C4), I then have to unhide all lines, manually select cell C5 and then it works, sort off, I'm close, just can't fathom how to push it over the line so to speak?

    I've attached latest workbook for reference.

     Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, rC3 As Range, rC4 As Range, rC5 As Range
    
    Set r = Target.Cells(1, 1)  'only check first cell
    Set rC3 = Range("C3")
    Set rC4 = Range("C4")
    Set rC5 = Range("C5")
            
    If r.Address <> rC3.Address And r.Address <> rC4.Address And r.Address <> rC5.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub


    If Target.Address = "$C$3" Then
        Range("C4").Value = "Please Select Origin..."
     End If
        
    Application.EnableEvents = False
    Unprotect Password:="dlm"
     
    Shapes("RFR20").Visible = False
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = False
    Shapes("Priority40").Visible = False
    Shapes("Priority40HQ").Visible = False
     
    Rows("5:25").Hidden = True
    Rows("79:81").Hidden = True
        
    Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value
    
Case "Air"
    Range("$C$5").Value = "Factory Load"
        
Case "Air" & "#" & "Warsaw to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A16").EntireRow.Hidden = True
    Range("A17").EntireRow.Hidden = False
    Range("A18:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    Range("A32:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Warsaw to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A16").EntireRow.Hidden = True
    Range("A17").EntireRow.Hidden = False
    Range("A18:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
            
Case "Air" & "#" & "Malpensa to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A12").EntireRow.Hidden = False
    Range("A13:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Malpensa to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Heathrow to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7").EntireRow.Hidden = False
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Heathrow to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7").EntireRow.Hidden = False
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US"
    Range("$C$5").Value = "Factory Load"
    
Case "Ocean_EU_to_US" & "#" & "Genoa to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A10").EntireRow.Hidden = True
    Range("A11").EntireRow.Hidden = False
    Range("A20").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A26:A32").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A10").EntireRow.Hidden = True
    Range("A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A26:A32").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A19").EntireRow.Hidden = False
    Range("A20:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "CFS Loading"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A19").EntireRow.Hidden = True
    Range("A20").EntireRow.Hidden = False
    Range("A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A19").EntireRow.Hidden = False
    Range("A20:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles" & "#" & "CFS Loading"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A20").EntireRow.Hidden = False
    Range("A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Hamburg to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A19").EntireRow.Hidden = False
    Range("A20:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US" & "#" & "Hamburg to New York" & "#" & "CFS Loading"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A20").EntireRow.Hidden = False
    Range("A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff




Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A19").EntireRow.Hidden = False
    Range("A20:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles" & "#" & "CFS Loading"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A20").EntireRow.Hidden = False
    Range("A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_Asia_to_EU"
    Range("$C$5").Value = "Factory Load"
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
        
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to SOU/FXT" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A20").EntireRow.Hidden = True
    Range("A21:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to SOU/FXT" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A20").EntireRow.Hidden = True
    Range("A21:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
            
Case "Overland"
    Range("$C$5").Value = "Factory Load"
            
Case "Overland" & "#" & "PL to UK" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7").EntireRow.Hidden = False
    Range("A8:A21").EntireRow.Hidden = True
    Range("A22:A23").EntireRow.Hidden = False
    Range("A24:A25").EntireRow.Hidden = True
    Range("A26").EntireRow.Hidden = False
    Range("A27:A32").EntireRow.Hidden = False
    Range("A38:A74").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
    
Case "Overland" & "#" & "UK to PL" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7:A13").EntireRow.Hidden = True
    Range("A14").EntireRow.Hidden = False
    Range("A15:A21").EntireRow.Hidden = True
    Range("A22:A23").EntireRow.Hidden = False
    Range("A24:A25").EntireRow.Hidden = True
    Range("A26").EntireRow.Hidden = False
    Range("A27:A32").EntireRow.Hidden = False
    Range("A38:A74").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
    
End Select
    
    Select Case rC4.Value
        Case "Genoa to New York"
            Shapes("RFR20").Visible = True
            Shapes("RFR40").Visible = True
            Shapes("RFR40HQ").Visible = True
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True


        Case "Genoa/La Spezia to Los Angeles"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Gdynia to New York"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Gdynia to Los Angeles"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Hamburg to New York"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Hamburg to Los Angeles"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "FXT/SOU to New York"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "FXT/SOU to Los Angeles"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Shanghai to Genoa"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Xiamen to Genoa"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Shanghai to Gdynia/Gdansk"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Xiamen to Gdynia/Gdansk"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
        
        Case "Shanghai to SOU/FXT"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
            
        Case "Xiamen to SOU/FXT"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = True
            Shapes("Priority40").Visible = True
            Shapes("Priority40HQ").Visible = True
        
        Case "UK to PL"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = False
            Shapes("Priority40").Visible = False
            Shapes("Priority40HQ").Visible = False
        
        Case "PL to UK"
            Shapes("RFR20").Visible = False
            Shapes("RFR40").Visible = False
            Shapes("RFR40HQ").Visible = False
            Shapes("Priority20").Visible = False
            Shapes("Priority40").Visible = False
            Shapes("Priority40HQ").Visible = False
            
    End Select
    
    ActiveSheet.Protect Password:="dlm"
    Application.EnableEvents = True
End Sub
    Attached Files Attached Files
    Last edited by Scuba; 09-21-2023 at 07:01 AM. Reason: VBA Code Changed
    Reply With Quote Reply With Quote
  20. 09-21-2023, 07:18 AM #60
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Hard to tell since you've modified what I suggested, but a quick look seems to be that in Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value

    the (rC3.Value & "#" & rC4.Value & "#" & rC5.Value) will never equal just "Air" so the Range("$C$5").Value = "Factory Load") will never execute

    Try adding the If outside of the Case Select



        If rC3.Value = "Air" Then rC5.Value = "Factory Load"
        
    Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value
'
'Case "Air"
'    Range("$C$5").Value = "Factory Load"
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules