Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code)

  1. #1
    New Member RavosJ's Avatar
    Join Date
    Aug 2019
    Location
    Barcelona
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code)

    Hi All,

    I am almost in the final stages of my code and I am very excited! So close to the end, I can smell it.
    Unfortunately this also means he code is getting trickier.

    What I want to accomplish:

    I have 2 important dropdowns with each 2 different options: 1. A / B - 2. C / D
    First Dropdown [E8]:


    1. When they select A, the rows 9 & 10 should disappear
    2. When they select B, the rows 9 & 10 should (re)appear

    Second Dropdown [E11]:


    1. When they select C, the rows 15 - 19 should disappear
    2. When theyselect D, the rows 15 - 19 should (re)appear


    This might seem not that hard, however, I need it to be dynamic and fit in my current coding:

    Sub Mod_SendWorkbook()
    Dim OutlookMail AsObject
    Set OutlookMail =CreateObject("Outlook.Application").CreateItem(0)

    '******validatefields******'

    IfRange("D7").Value = "" Then
    MsgBox"Please Select Value on row 7"
    Exit Sub
    End If
    IfRange("E8").Value = "" Then
    MsgBox"Please Select Value on row 8"
    Exit Sub
    End If
    IfRange("E11").Value = "" Then
    MsgBox"Please Select Value on row 11"
    Exit Sub
    End If
    IfRange("D12").Value = "" Then
    MsgBox"Please Select Value on row 12"
    Exit Sub
    End If
    IfRange("D14").Value = "" Then
    MsgBox"Please select value on row 14"
    Exit Sub
    End If
    IfRange("D15").Value = "" Then
    MsgBox"Please Select Value on row 15"
    Exit Sub
    End If
    IfRange("D17").Value = "" Then
    MsgBox"Please Select Value on row 17"
    Exit Sub
    End If
    IfRange("D18").Value = "" Then
    MsgBox"Please Select Value on row 18"
    Exit Sub
    End If
    If Range("D19").Value = "" Then
    MsgBox"Please Select Value on row 19"
    Exit Sub

    '******validatefields******'


    On Error Resume Next

    With OutlookMail
    .To = "xxxxx@gmail.com"
    .CC = ""
    .BCC =""
    .Subject =Range("D7").Text
    .Body ="Please check attached file, thank you."
    .Attachments.AddApplication.ActiveWorkbook.FullName
    .Send
    If Err.Number = 0Then
    MsgBox"sent successfully"
    Else
    MsgBox"Sent error: " & Err.Number & " Description: "& Err.Description
    End If
    End With
    Set OutlookMail =Nothing
    End Sub




    Any thoughts, help or direction would be highly appreciated!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Try this


    Code:
    Sub Mod_SendWorkbook()
      Dim OutlookMail As Object, msg As String
      '******validatefields******'
      If Range("D7").Value = "" Then msg = "Please Select Value on row 7"
      Select Case Range("E8").Value
        Case "":  msg = "Please Select Value on row 8"
        Case "A": Rows("9:10").Hidden = True
        Case "B": Rows("9:10").Hidden = False
      End Select
      Select Case Range("E11").Value
        Case "":  msg = "Please Select Value on row 8"
        Case "C": Rows("15:19").Hidden = True
        Case "D": Rows("15:19").Hidden = False
      End Select
      If Range("D12").Value = "" Then msg = "Please Select Value on row 12"
      If Range("D14").Value = "" Then msg = "Please select value on row 14"
      If Range("D15").Value = "" Then msg = "Please Select Value on row 15"
      If Range("D17").Value = "" Then msg = "Please Select Value on row 17"
      If Range("D18").Value = "" Then msg = "Please Select Value on row 18"
      If Range("D19").Value = "" Then msg = "Please Select Value on row 19"
      '******Send mail******'
      If msg <> "" Then
        MsgBox msg
        Exit Sub
      End If
      On Error Resume Next
      Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
      With OutlookMail
        .To = "xxxxx@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = Range("D7").Text
        .Body = "Please check attached file, thank you."
        .Attachments.Add Application.ActiveWorkbook.FullName
        .Send
        If Err.Number = 0 Then
          MsgBox "sent successfully"
        Else
          MsgBox "Sent error: " & Err.Number & " Description: " & Err.Description
        End If
      End With
      Set OutlookMail = Nothing
    End Sub

    Note: Use code tag to enclose code, press # icon (Wrap CODE tags around selected text)
    Regards Dante Amor

  3. #3
    New Member RavosJ's Avatar
    Join Date
    Aug 2019
    Location
    Barcelona
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Hi Dante,

    This is great!
    The only problem I am facing now is that the action is not taking place when the actual value is selected in Excel.
    It only works when I select it and run the Macro in visual basics.
    Any ideas? I am also looking on the forum to see if I can find the answer in the meantime.
    Thanks in advance.

  4. #4
    New Member RavosJ's Avatar
    Join Date
    Aug 2019
    Location
    Barcelona
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Also just noticed that because some fields are mandatory, when they are hidden, they should be ignored....
    Otherwise there is a message saying to fill in a field that is hidden...

    Help or guidance is much appreciated!

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    hi, RavosJ. Try this:
    It's a Worksheet_Change event Sub.
    Copy the code then right click the sheet tab (the sheet where the data validation is located) > select View Code > paste the code.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.CountLarge <> 1 Then Exit Sub
        If Not Intersect(Target, Union(Range("E8"), Range("E11"))) Is Nothing Then
    
            Select Case Target.Value
                Case "A": Rows("9:10").Hidden = True
                Case "B": Rows("9:10").Hidden = False
                Case "C": Rows("15:19").Hidden = True
                Case "D": Rows("15:19").Hidden = False
            End Select
            
            Dim ary, z
            ary = Split("D7 E8 E11 D12 D14 D15 D17 D18 D19")
            For Each z In ary
                If Range(z) = "" Then MsgBox "Please Select Value on row" & Range(z).Row: Exit For
            Next
            
            
        End If
    End Sub

    Also just noticed that because some fields are mandatory, when they are hidden, they should be ignored....
    Otherwise there is a message saying to fill in a field that is hidden...
    But it doesn't take into account your new requirement. Could you explain it in more detail?
    But first, try the code, see if it works for your original requirement.

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    EDIT:
    OOPS, Sorry, I missed the last part about the OutlookMail.

  7. #7
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Ok, try this one instead:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.CountLarge <> 1 Then Exit Sub
        If Not Intersect(Target, Union(Range("E8"), Range("E11"))) Is Nothing Then
    
            Select Case Target.Value
                Case "A": Rows("9:10").Hidden = True
                Case "B": Rows("9:10").Hidden = False
                Case "C": Rows("15:19").Hidden = True
                Case "D": Rows("15:19").Hidden = False
            End Select
            
            Dim ary, z
            ary = Split("D7 E8 E11 D12 D14 D15 D17 D18 D19")
            For Each z In ary
                If Range(z) = "" Then MsgBox "Please Select Value on row" & Range(z).Row: Exit Sub
            Next
            
            On Error Resume Next
            
            With OutlookMail
                .To = "xxxxx@gmail.com"
                .CC = ""
                .BCC = ""
                .Subject = Range("D7").Text
                .Body = "Please check attached file, thank you."
                .Attachments.AddApplication.ActiveWorkbook.FullName
                .Send
                    If Err.Number = 0 Then
                    MsgBox "sent successfully"
                    Else
                    MsgBox "Sent error: " & Err.Number & " Description: " & Err.Description
                    End If
            End With
                Set OutlookMail = Nothing
        End If
    
    
    End Sub
    

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Hi @RavosJ ,
    Quote Originally Posted by RavosJ View Post
    Also just noticed that because some fields are mandatory, when they are hidden, they should be ignored....
    Otherwise there is a message saying to fill in a field that is hidden...

    Put the following code in the events of the sheet

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, Range("E8, E11")) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        Dim OutlookMail As Object, msg As String
        '******validatefields******'
        If Range("D7").Value = "" Then msg = "Please Select Value on row 7"
        Select Case Range("E8").Value
          Case "":  msg = "Please Select Value on row 8"
          Case "A": Rows("9:10").Hidden = True
          Case "B": Rows("9:10").Hidden = False
        End Select
        Select Case Range("E11").Value
          Case "":  msg = "Please Select Value on row 8"
          Case "C": Rows("15:19").Hidden = True
          Case "D": Rows("15:19").Hidden = False
        End Select
        If Range("D12").Value = "" Then msg = "Please Select Value on row 12"
        If Range("D14").Value = "" Then msg = "Please select value on row 14"
        If Range("D15").EntireRow.Hidden = False Then _
          If Range("D15").Value = "" Then msg = "Please Select Value on row 15"
        If Range("D17").EntireRow.Hidden = False Then _
          If Range("D17").Value = "" Then msg = "Please Select Value on row 17"
        If Range("D18").EntireRow.Hidden = False Then _
          If Range("D18").Value = "" Then msg = "Please Select Value on row 18"
        If Range("D19").EntireRow.Hidden = False Then _
          If Range("D19").Value = "" Then msg = "Please Select Value on row 19"
        '******Send mail******'
        If msg <> "" Then
          MsgBox msg
          Exit Sub
        End If
        On Error Resume Next
        Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
        With OutlookMail
          .To = "xxxxx@gmail.com"
          .CC = ""
          .BCC = ""
          .Subject = Range("D7").Text
          .Body = "Please check attached file, thank you."
          .Attachments.Add Application.ActiveWorkbook.FullName
          .Send
          If Err.Number = 0 Then
            MsgBox "sent successfully"
          Else
            MsgBox "Sent error: " & Err.Number & " Description: " & Err.Description
          End If
        End With
        Set OutlookMail = Nothing
      End If
    End Sub

    SHEET EVENT
    Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
    Regards Dante Amor

  9. #9
    New Member RavosJ's Avatar
    Join Date
    Aug 2019
    Location
    Barcelona
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Hi,

    Thanks Dante!

    De code is working fine, the only thing that is not working as suspected, is when the file is sent.
    It is empty, so it does no transfer the filled in data to the e-mail.

    Any solutions, tips or advice here?

    KR,

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

    Quote Originally Posted by RavosJ View Post
    Hi,

    Thanks Dante!

    De code is working fine, the only thing that is not working as suspected, is when the file is sent.
    It is empty, so it does no transfer the filled in data to the e-mail.

    Any solutions, tips or advice here?

    KR,
    Try this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, Range("E8, E11")) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        Dim OutlookMail As Object, msg As String
        '******validatefields******'
        If Range("D7").Value = "" Then msg = "Please Select Value on row 7"
        Select Case Range("E8").Value
          Case "":  msg = "Please Select Value on row 8"
          Case "A": Rows("9:10").Hidden = True
          Case "B": Rows("9:10").Hidden = False
        End Select
        Select Case Range("E11").Value
          Case "":  msg = "Please Select Value on row 8"
          Case "C": Rows("15:19").Hidden = True
          Case "D": Rows("15:19").Hidden = False
        End Select
        If Range("D12").Value = "" Then msg = "Please Select Value on row 12"
        If Range("D14").Value = "" Then msg = "Please select value on row 14"
        If Range("D15").EntireRow.Hidden = False Then _
          If Range("D15").Value = "" Then msg = "Please Select Value on row 15"
        If Range("D17").EntireRow.Hidden = False Then _
          If Range("D17").Value = "" Then msg = "Please Select Value on row 17"
        If Range("D18").EntireRow.Hidden = False Then _
          If Range("D18").Value = "" Then msg = "Please Select Value on row 18"
        If Range("D19").EntireRow.Hidden = False Then _
          If Range("D19").Value = "" Then msg = "Please Select Value on row 19"
        '******Send mail******'
        If msg <> "" Then
          MsgBox msg
          Exit Sub
        End If
        On Error Resume Next
        ThisWorkbook.Save
        Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
        With OutlookMail
          .To = "xxxxx@gmail.com"
          .CC = ""
          .BCC = ""
          .Subject = Range("D7").Text
          .Body = "Please check attached file, thank you."
          .Attachments.Add Application.ActiveWorkbook.FullName
          .Send
          If Err.Number = 0 Then
            MsgBox "sent successfully"
          Else
            MsgBox "Sent error: " & Err.Number & " Description: " & Err.Description
          End If
        End With
        Set OutlookMail = Nothing
      End If
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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