Solved Creating Macros Including Dropdown Menu

March 21, 2017 at 15:21:35
Specs: Windows 10
I am using Excel 2016 and trying to create a macro. When a option on the dropdown list in column D has been chosen, I want the macro to add a Row and create a dropdown list in Column D from another workbook list.

Example: Do we need to remove flooring (dropdown: Yes, No) If yes than add another Row asking what kind of flooring (dropdown: Tile, Hardwood)

I tried using this code (see below) but I do not want to copy the line above. I want it to give me another line and give me a different dropdown menu than the line above.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHnd
'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub
'Determine if the changed cell is in Column A and is a X
If Target.Column = 1 Then
If Target = "X" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
Target.EntireRow.Copy
Range("A" & Target.Row + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If
End If
errHnd:
'Re-enable event
Application.EnableEvents = True
End Sub

message edited by Brad93


See More: Creating Macros Including Dropdown Menu

Report •

✔ Best Answer
March 31, 2017 at 06:28:40
Here's an example of how the code could be written for "Remove Flooring" and "Remove Drywall". Each job has it's own snippet that creates the new row, sets up the Data Validation and formulas. The ranges need to be set correctly and the formulas may need to be customized for each type of job.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
  
'Determine type of job chosen in Column A

'***** Flooring Snippet *****

   If Target = "Remove Flooring" Then
    Application.EnableEvents = False
'Insert Row, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Target.Offset(1, 0) = "Choose A Flooring Type"
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet3!$N$1:$N$3"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
'Place VLOOKUP and Multiplication formulas in ColumnS E & F
'IFERROR is used to suppress errors until Flooring Type is chosen from new drop down
    Target.Offset(1, 4).Formula = _
     "=IFERROR(VLOOKUP(" & Target.Offset(1, 0).Address & ",Sheet3!$N$1:$O$3,2,0),"""")"
    Target.Offset(1, 5).Formula = _
     "=IFERROR(D" & Target.Row + 1 & "*E" & Target.Row + 1 & ","""")"
    Application.EnableEvents = True
   End If
  
'***** Drywall Snippet *****
   
   If Target = "Remove Drywall" Then
    Application.EnableEvents = False
'Insert Row, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Target.Offset(1, 0) = "Choose A Drywall Job"
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet3!$P$1:$P$3"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
'Place VLOOKUP and Multiplication formulas in ColumnS E & F
'IFERROR is used to suppress errors until Drywall Type is chosen from new drop down
    Target.Offset(1, 4).Formula = _
     "=IFERROR(VLOOKUP(" & Target.Offset(1, 0).Address & ",Sheet3!$P$1:$Q$3,2,0),"""")"
    Target.Offset(1, 5).Formula = _
     "=IFERROR(D" & Target.Row + 1 & "*E" & Target.Row + 1 & ","""")"
    Application.EnableEvents = True
   End If
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 21, 2017 at 16:20:32
Thanks for letting us know what you want. If you need some help with that, feel free to ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 21, 2017 at 16:27:28
DerbyDad03, Can you please write me a code to see that I can add a line as per my requirements above?

Report •

#3
March 22, 2017 at 10:17:52
The following code is an example of how to Insert a row and add a Validation Drop Down list below the target cell when Yes is entered in the cell.

Obviously you will have to change the range that contains the values for the Drop Down.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Yes was chosen in Column D
If Target.Column = 4 Then
  If Target = "Yes" Then
   Application.EnableEvents = False
'Insert Roww, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=$N$1:$N$6"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
   Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
March 22, 2017 at 11:14:18
DerbyDad03 you are the best! Thank you SOOOO much! I may need more of your help in the near future!

Report •

#5
March 22, 2017 at 13:14:54
DerbyDad03, what if I wanted my code to access a list on a different sheet?

Also, how would I modify the code to reflect this...
If Hardwood floor is chosen than this list
If ceramic floor is chosen than this list

Lists being located on another sheet. If you could modify the code to reflect those needs that would almost complete my project. I appreciate the help.

message edited by Brad93


Report •

#6
March 22, 2017 at 20:44:01
re: "what if I wanted my code to access a list on a different sheet?"

Just reference the sheet name...

Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet2!$N$1:$N$6"

re: "If Hardwood floor is chosen than this list
If ceramic floor is chosen than this list
"

I don't understand your question based on what you asked for in your original post.

You said you wanted a Row and a List added when Yes was chosen. Now you appear to be asking for another list once a value from the "new" list is chosen. However, you don't say where that list is supposed to go. i.e if the user chooses Hardwood where is the list associated with Hardwood supposed to go?

Maybe this will help... A drop down list that is populated based on the value chosen from a previous list is known as a Dependent Drop Down List. An good explanation of how to create them can be found here:

http://www.contextures.com/xlDataVa...

BTW...I will not have access to Excel for the next 5 days, so don't expect much help from me until the middle of next week. I may be able to answer general questions as time allows, but I won't be able to write or test any code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 23, 2017 at 06:26:38
Ok sorry for the mix up! This is what I need.

-When X is chosen in Column A to reflect "Remove Flooring",
-It should add a Row below. In that Row in Column C, I want the list from Sheet 3 in a drop down menu. That list will ask "ceramic, hardwood, cushion..."
- In that same "new row" in Column D I will enter the quantity of flooring that needs to be remove example: 100sq.ft.
- In that same "new row" in Column E I want to pull the price associate to either the ceramic, hardwood or cushion flooring in sheet 3. Example: $7/sq.ft
- In Column F of the "new Row" will calculate the quantity in row D x the cost in Row E.

Hopefully that clarifies my project for you. If you could write a code for me upon your return that would be great!

message edited by Brad93


Report •

#8
March 30, 2017 at 08:54:08
re: When X is chosen in Column A to reflect "Remove Flooring"

You seemed to have changed your original requirements. Originally you asked about inserting a new line and drop down when Yes was chosen in Column D. Now you are asking about Column A showing "Remove Flooring".

It is difficult (and frustrating) to provide a solution if your requirements keep changing. Please keep in mind that we can't see your workbook from where we are sitting nor do we know the process that you follow when using the workbook. VBA code has to be very specific with its instructions so unless you understand VBA and can make modifications yourself, we can't offer a complete solution unless you provide the complete requirements.

I have attempted to meet the requirements stated in Response #7 with the code offered below.

re: -It should add a Row below. In that Row in Column C, I want the list from Sheet 3 in a drop down menu. That list will ask "ceramic, hardwood, cushion..."

The code included at the bottom of this post will insert a row when "Remove Flooring" is entered or chosen in Column A. It will place a Drop List in the new row in Column A and populate that list with data from Sheet3!N1:N3.

re: - In that same "new row" in Column D I will enter the quantity of flooring that needs to be remove example: 100sq.ft.

That sounds like a manual entry, so it's nothing that I need to address.

re: - In that same "new row" in Column E I want to pull the price associate to either the ceramic, hardwood or cushion flooring in sheet 3. Example: $7/sq.ft

I don't know where you are pulling the price data from, so I will assume that you have a table someplace that contains the pricing. The code below will insert a VLOOKUP function in Column E of the new row. Since I don't know where your pricing table is, I am assuming it is on Sheet3 and uses the same list as the drop down for the new row. I have Flooring types in Sheet3!N1:N3 and pricing in Sheet3!O1:O3. Therefore the table_array for the VLOOKUP is Sheet3!N1:O3. Please note that the value that is "pulled in" has to be a value that Excel can perform a mathematical operation on. You can't multiply $7/sq.ft by a value in another cell, but you can multiply 7.

You will obviously need to modify that instruction so that the formula references the range that contains your pricing data.

re: In Column F of the "new Row" will calculate the quantity in row D x the cost in Row E.

The code below will place the proper formula in Column F of the new Row.

NOTE: I wrapped the IFERROR function around both the formulas for the new row. This is to suppress the errors that would show up before a value is chosen from the new drop down.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if "Remove Flooring" was chosen in Column A
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   If Target = "Remove Flooring" Then
    Application.EnableEvents = False
'Insert Row, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Target.Offset(1, 0) = "Choose A Flooring Type"
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet3!$N$1:$N$3"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
'Place VLOOKUP and Multiplication formulas in ColumnS E & F
'IFERROR is used to suppress errors until Flooring Type is chosen from new drop down
    Target.Offset(1, 4).Formula = _
     "=IFERROR(VLOOKUP(" & Target.Offset(1, 0).Address & ",Sheet3!$N$1:$O$3,2,0),"""")"
    Target.Offset(1, 5).Formula = _
     "=IFERROR(D" & Target.Row + 1 & "*E" & Target.Row + 1 & ","""")"
    Application.EnableEvents = True
   End If
  End If
 End If
End Sub

Finally, please consider reviewing the debugging techniques found via this link:

https://www.computing.net/howtos/sh...

They may help you modify/trouble shoot your VBA code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
March 30, 2017 at 16:37:23
ok sorry I am new at this (asking for help on a forum) so I will try to be more clear.

This is GREAT, thanks for reading through the confusing question. My last little question for you is... I am going to have various types of removal. Flooring, Drywall, Electrical and others... they all need different options in the drop down menu.

So this is the way I have my sheet laid out...

Tear Out Labour to do Description Quantity Cost

Drywall

Remove Flooring
Ceramic 0.25

Electrical

What you gave me is good if everything on the sheet was removing flooring.
If I want a different drop down for Electrical or Drywall how would I write a code that will give me a drop down option based on the type or removal I am doing.

All my lists will be in sheet 3...

I hope this clears it up... I am new at writing macro and a little out of my league.


Report •

#10
March 30, 2017 at 19:52:04
re: What you gave me is good if everything on the sheet was removing flooring.

Don’t take this the wrong way, but I gave you what you asked for. I don't read minds so there was no way for me to know that you had more than one category of job.

re: If I want a different drop down for Electrical or Drywall how would I write a code that will give me a drop down option based on the type of removal I am doing.

To be honest, I'm not quite sure what to do next. I've done the groundwork for you by providing the code required for one of your job categories - the only category you asked for, by the way.

To answer your question… "how would I write a code...":

You would basically replicate what I wrote for each category, referencing the ranges for each list and pricing category.

If Target = "Flooring" then do this… (I've done this part for you)
If Target = "Electrical" then do this…
If Target = "Drywall" then do this…

I’ll repeat the link that can be used to reverse engineer my code so you can figure out how it works. I’m also willing to explain it, but I think I covered that in my previous post as well as in the comments included in the code.

https://www.computing.net/howtos/sh...

My gut feeling is that you have more on your spreadsheet than what you are telling me. I also have a feeling that you won't be using the exact same formulas for all of your jobs. In other words I somehow doubt the same VLOOKUP and simple multiplication formulas will work for an Electrical job as for a Flooring job. i.e You are not removing wiring by the square foot are you? There is no way I can code all of this for you without knowing all of the specifics and ranges, etc.

Without your exact layout and all of your data, there is no way for me to supply a complete solution. Hopefully the sample code I provided will lead you in the right direction.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
March 31, 2017 at 06:28:40
✔ Best Answer
Here's an example of how the code could be written for "Remove Flooring" and "Remove Drywall". Each job has it's own snippet that creates the new row, sets up the Data Validation and formulas. The ranges need to be set correctly and the formulas may need to be customized for each type of job.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
  
'Determine type of job chosen in Column A

'***** Flooring Snippet *****

   If Target = "Remove Flooring" Then
    Application.EnableEvents = False
'Insert Row, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Target.Offset(1, 0) = "Choose A Flooring Type"
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet3!$N$1:$N$3"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
'Place VLOOKUP and Multiplication formulas in ColumnS E & F
'IFERROR is used to suppress errors until Flooring Type is chosen from new drop down
    Target.Offset(1, 4).Formula = _
     "=IFERROR(VLOOKUP(" & Target.Offset(1, 0).Address & ",Sheet3!$N$1:$O$3,2,0),"""")"
    Target.Offset(1, 5).Formula = _
     "=IFERROR(D" & Target.Row + 1 & "*E" & Target.Row + 1 & ","""")"
    Application.EnableEvents = True
   End If
  
'***** Drywall Snippet *****
   
   If Target = "Remove Drywall" Then
    Application.EnableEvents = False
'Insert Row, Add Validation to new row
    Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Target.Offset(1, 0) = "Choose A Drywall Job"
      With Target.Offset(1, 0).Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet3!$P$1:$P$3"
         .IgnoreBlank = True
         .InCellDropdown = True
      End With
'Place VLOOKUP and Multiplication formulas in ColumnS E & F
'IFERROR is used to suppress errors until Drywall Type is chosen from new drop down
    Target.Offset(1, 4).Formula = _
     "=IFERROR(VLOOKUP(" & Target.Offset(1, 0).Address & ",Sheet3!$P$1:$Q$3,2,0),"""")"
    Target.Offset(1, 5).Formula = _
     "=IFERROR(D" & Target.Row + 1 & "*E" & Target.Row + 1 & ","""")"
    Application.EnableEvents = True
   End If
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
March 31, 2017 at 11:26:06
And that is exactly what I wanted. Can't thank you for your help...

Report •

#13
March 31, 2017 at 12:04:36
I'm glad I could help.

I want to warn you about something very important, based on the assumption that you are going to be modifying/writing the code.

First, the background:

You will note that each snippet contains these 2 instructions:


Application.EnableEvents = False
...
...
Application.EnableEvents = True

The False instruction stops the code from firing again when the code itself makes a change to the sheet. Since a Worksheet_Change macro runs at every change to the sheet, it will fire when the Row is added and fire each time the code places the formulas in the cells. The False instruction lets the code make changes without the macro firing again and again while it is still making changes.

The True instruction Enables Events again so that the code will run the next time you make a manual change to the sheet, which is what you want it to do.

The warning:

If the code fails after the False instruction is executed but before the True instruction is executed, Events will be disabled and nothing will happen when you make a change to the sheet. This can happen quite often when you are writing code and it throws up an error, causing you to stop the macro. The next time you make a change to the sheet, you might think that the code is not working when in reality it isn't even running because Events have been disabled. There is no warning or indication that Events are disabled, other than the fact that Event driven macros don't run.

There are 3 options for re-enabling Events if that happens:

1 - Brute force - Quit excel and restart it. (Not usually the best option.)

2 - With a simple macro -

Stick this code in the VBA editor and run it whenever you need to re-enable Events:

Sub ResetEvents()
  Application.EnableEvents = True
End Sub

3 - The VBA Immediate window - The VBA Editor (VBE) has a feature know as the Immediate Window. It can be used to run instructions without them being part of a macro. If you put this instruction (or any instruction) in the Immediate window, you can just put your cursor on it and hit enter. The instruction will run and Events will be enabled:

Application.EnableEvents = True

Obviously you can also put the False instruction in the Immediate window so that you can turn Events off so you can make changes to the sheet without the code running every time. Just make sure to turn Events back on when you are done.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
April 26, 2017 at 09:57:25
Derbydad03,

To follow up on the previous code you wrote for me, everything is working out perfectly. My next question for you is...

When the estimate is being created and a new line is added, the estimator must fill out information in the new row in column B,C,D & E. I would like cells in the new row highlighted until the information is imputed. I do not want the estimator to over look any cells.

Is this possible based on the previous code and if so could you modify it to reflect this change.


Report •

Ask Question