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

RavosJ

New Member
Joined
Aug 27, 2019
Messages
8
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Union(Range([COLOR=brown]"E8"[/COLOR]), Range([COLOR=brown]"E11"[/COLOR]))) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]

        [COLOR=Royalblue]Select[/COLOR] [COLOR=Royalblue]Case[/COLOR] Target.Value
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"A"[/COLOR]: Rows([COLOR=brown]"9:10"[/COLOR]).Hidden = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"B"[/COLOR]: Rows([COLOR=brown]"9:10"[/COLOR]).Hidden = [COLOR=Royalblue]False[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"C"[/COLOR]: Rows([COLOR=brown]"15:19"[/COLOR]).Hidden = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"D"[/COLOR]: Rows([COLOR=brown]"15:19"[/COLOR]).Hidden = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Select[/COLOR]
        
        [COLOR=Royalblue]Dim[/COLOR] ary, z
        ary = Split([COLOR=brown]"D7 E8 E11 D12 D14 D15 D17 D18 D19"[/COLOR])
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] z [COLOR=Royalblue]In[/COLOR] ary
            [COLOR=Royalblue]If[/COLOR] Range(z) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR] MsgBox [COLOR=brown]"Please Select Value on row"[/COLOR] & Range(z).Row: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        
        
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]


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.
 
Upvote 0
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.:confused:
 
Upvote 0
Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

Ok, try this one instead:
Code:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Union(Range([COLOR=brown]"E8"[/COLOR]), Range([COLOR=brown]"E11"[/COLOR]))) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]

        [COLOR=Royalblue]Select[/COLOR] [COLOR=Royalblue]Case[/COLOR] Target.Value
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"A"[/COLOR]: Rows([COLOR=brown]"9:10"[/COLOR]).Hidden = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"B"[/COLOR]: Rows([COLOR=brown]"9:10"[/COLOR]).Hidden = [COLOR=Royalblue]False[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"C"[/COLOR]: Rows([COLOR=brown]"15:19"[/COLOR]).Hidden = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"D"[/COLOR]: Rows([COLOR=brown]"15:19"[/COLOR]).Hidden = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Select[/COLOR]
        
        [COLOR=Royalblue]Dim[/COLOR] ary, z
        ary = Split([COLOR=brown]"D7 E8 E11 D12 D14 D15 D17 D18 D19"[/COLOR])
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] z [COLOR=Royalblue]In[/COLOR] ary
            [COLOR=Royalblue]If[/COLOR] Range(z) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR] MsgBox [COLOR=brown]"Please Select Value on row"[/COLOR] & Range(z).Row: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        
        [COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]Resume[/COLOR] [COLOR=Royalblue]Next[/COLOR]
        
        [COLOR=Royalblue]With[/COLOR] OutlookMail
            .[COLOR=Royalblue]To[/COLOR] = [COLOR=brown]"xxxxx@gmail.com"[/COLOR]
            .CC = [COLOR=brown]""[/COLOR]
            .BCC = [COLOR=brown]""[/COLOR]
            .Subject = Range([COLOR=brown]"D7"[/COLOR]).[COLOR=Royalblue]Text[/COLOR]
            .Body = [COLOR=brown]"Please check attached file, thank you."[/COLOR]
            .Attachments.AddApplication.ActiveWorkbook.FullName
            .Send
                [COLOR=Royalblue]If[/COLOR] Err.Number = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                MsgBox [COLOR=brown]"sent successfully"[/COLOR]
                [COLOR=Royalblue]Else[/COLOR]
                MsgBox [COLOR=brown]"Sent error: "[/COLOR] & Err.Number & [COLOR=brown]" Description: "[/COLOR] & Err.Description
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
            [COLOR=Royalblue]Set[/COLOR] OutlookMail = [COLOR=Royalblue]Nothing[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]
 
Upvote 0
Re: If Value X is selected Rows 1 / 2 will be hidden (+DYNAMIC with previous code) [NOT EASY]

Hi @RavosJ ,
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.
 
Upvote 0
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,
 
Upvote 0
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,

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
[COLOR=#0000ff]    ThisWorkbook.Save[/COLOR]
    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
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,612
Members
452,661
Latest member
Nonhle

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