G
Guest
I have a list that I want to make into a drop down list that when each
individual item is selected, cause a macro to run. Can anyone assist?
individual item is selected, cause a macro to run. Can anyone assist?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Ron de Bruin
Hi Ajgoodson
You can use Data>Validation to create the list
http://www.contextures.com/xlDataVal01.html
Then you can use the Change event to do what you want
http://www.cpearson.com/excel/events.htm
Example for cell A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
'call your macro here
End If
End Sub
You can use Data>Validation to create the list
http://www.contextures.com/xlDataVal01.html
Then you can use the Change event to do what you want
http://www.cpearson.com/excel/events.htm
Example for cell A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
'call your macro here
End If
End Sub
G
Guest
I got the list created from the data valuation. Thanks!
However, I am not familiar with the events thing you mentioned. I am
looking to:
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4.
Can this be done through the events, or some other way?
Thank you so very much for your help!
However, I am not familiar with the events thing you mentioned. I am
looking to:
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4.
Can this be done through the events, or some other way?
Thank you so very much for your help!
R
Ron de Bruin
Hi Ajgoodson
Right click on the sheet tab and choose "View Code"
Paste this event
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A3"), Target) Is Nothing Then
Select Case Target.Value
Case 1: Range("A4:C4").Value = Range("A8:C8").Value
Case 2: Range("A4:C4").Value = Range("A9:C9").Value
End Select
End If
End Sub
Alt q to go back to Excel
Try to change A3 now
Right click on the sheet tab and choose "View Code"
Paste this event
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A3"), Target) Is Nothing Then
Select Case Target.Value
Case 1: Range("A4:C4").Value = Range("A8:C8").Value
Case 2: Range("A4:C4").Value = Range("A9:C9").Value
End Select
End If
End Sub
Alt q to go back to Excel
Try to change A3 now
G
Guest
I got that to work. But it will not let me change the numbers 1 and 2 to
words. Can I?
words. Can I?
R
Ron de Bruin
Case "yourword" : Range("A4:C4").Value = Range("A8:C8").Value
G
Guest
YOU ARE GREAT!
I have just one more question.
What if the ranges are both on different tabs? i.e. tab1 and tab2?
Thank you so much!
I have just one more question.
What if the ranges are both on different tabs? i.e. tab1 and tab2?
Thank you so much!
R
Ron de Bruin
No problem if you have the values of
Range("A8:C8") and Range("A9:C9") on another tab
Use this then
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Value
Range("A8:C8") and Range("A9:C9") on another tab
Use this then
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Value
G
Guest
IT WORKS! IT WORKS!
Thank you again...Have a great day!
Thank you again...Have a great day!
T
Torb
Hi,
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
D
Don Guillett
Building on "this" doesn't tell us what "this" is. You should always stay in
the original thread so we will know what you are talking about....
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...
the original thread so we will know what you are talking about....
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...
M
Martincito23
Hello,
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
D
Don Guillett
Since you are familiar with worksheet events, you may like this better than
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
R
Rob Campbell
I have cell that includes an 8 item dropdown list (via data validation) and I would like to manage 8 scenarios from this list.
For example if I have scenario 1 selected I would like:
12% to appear in cell A1
$1,000 to appear in cell A2
50% to appear in cell A3
Scenario 2 would be three different, but known, values in the same three cells and so forth for the 6 other scenarios.
I realize that this can be done via if statements but I would like to increase the functionality of the scenarios in that, for scenario 1, the base case is 12%, $1,000 and 50% but I'd like to be able to type over, for example the 12%, with 10%.
Then, if I change to scenario 2 the cells update and when I change back to scenario 1, 12%, $1,000 and 50% reappear.
So, if I had used if statements, when typing over the if statement in cell A1 which would yield 12% with a 10% (hard-coded) the if statement would be gone and switching the dropdown list to scenario 1 would not work.
Thank you very much for your help.
Ajgoodso wrote:
Excel, Macro, Drop down list
14-Mar-07
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Previous Posts In This Thread:
Excel, Macro, Drop down list
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Re: Excel, Macro, Drop down list
Hi Ajgoodso
You can use Data>Validation to create the lis
http://www.contextures.com/xlDataVal01.htm
Then you can use the Change event to do what you wan
http://www.cpearson.com/excel/events.ht
Example for cell A
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A1"), Target) Is Nothing The
'call your macro her
End I
End Su
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got the list created from the data valuation. Thanks!
I got the list created from the data valuation. Thanks
However, I am not familiar with the events thing you mentioned. I am
looking to
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4
Can this be done through the events, or some other way
Thank you so very much for your help
:
Hi AjgoodsonRight click on the sheet tab and choose "View Code"Paste this
Hi Ajgoodso
Right click on the sheet tab and choose "View Code
Paste this even
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A3"), Target) Is Nothing The
Select Case Target.Valu
Case 1: Range("A4:C4").Value = Range("A8:C8").Valu
Case 2: Range("A4:C4").Value = Range("A9:C9").Valu
End Selec
End I
End Su
Alt q to go back to Exce
Try to change A3 no
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got that to work.
I got that to work. But it will not let me change the numbers 1 and 2 t
words. Can I
:
Re: Excel, Macro, Drop down list
Case "yourword" : Range("A4:C4").Value = Range("A8:C8").Valu
-
Regards Ron de Brui
http://www.rondebruin.nl/tips.htm
YOU ARE GREAT!
YOU ARE GREAT
I have just one more question
What if the ranges are both on different tabs? i.e. tab1 and tab2
Thank you so much!
:
Re: Excel, Macro, Drop down list
No problem if you have the values of
Range("A8:C8") and Range("A9:C9") on another tab
Use this then
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Value
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
Re: Excel, Macro, Drop down list
IT WORKS! IT WORKS!
Thank you again...Have a great day!
:
Building on this
Hi,
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
Hello,I took Ajgoodson and Ron example's below and tried to make a few
Hello,
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
:
Since you are familiar with worksheet events, you may like this better than
Since you are familiar with worksheet events, you may like this better than
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Excel, Macro, Drop down list
I have a question. I want two use two drop down menus one of whose list contents should depend on the selection in the first drop down menu.
For example suppose a1(list1) has 3 cases(1,2,3) if i select case 1, I want a2(list2)'s contents to be
range(a8:a11). If i select case 2, I want a2(list2)'s contents to be range(b8:b11). And finally if i select case 3, I want a2(list2)'s contents to be range(c8:c11).
How do I go about to do that?
Please note that by contents I mean source for list.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Create Hidden SharePoint List for Site Owners
http://www.eggheadcafe.com/tutorial...60-917a85a41b08/create-hidden-sharepoint.aspx
For example if I have scenario 1 selected I would like:
12% to appear in cell A1
$1,000 to appear in cell A2
50% to appear in cell A3
Scenario 2 would be three different, but known, values in the same three cells and so forth for the 6 other scenarios.
I realize that this can be done via if statements but I would like to increase the functionality of the scenarios in that, for scenario 1, the base case is 12%, $1,000 and 50% but I'd like to be able to type over, for example the 12%, with 10%.
Then, if I change to scenario 2 the cells update and when I change back to scenario 1, 12%, $1,000 and 50% reappear.
So, if I had used if statements, when typing over the if statement in cell A1 which would yield 12% with a 10% (hard-coded) the if statement would be gone and switching the dropdown list to scenario 1 would not work.
Thank you very much for your help.
Ajgoodso wrote:
Excel, Macro, Drop down list
14-Mar-07
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Previous Posts In This Thread:
Excel, Macro, Drop down list
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Re: Excel, Macro, Drop down list
Hi Ajgoodso
You can use Data>Validation to create the lis
http://www.contextures.com/xlDataVal01.htm
Then you can use the Change event to do what you wan
http://www.cpearson.com/excel/events.ht
Example for cell A
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A1"), Target) Is Nothing The
'call your macro her
End I
End Su
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got the list created from the data valuation. Thanks!
I got the list created from the data valuation. Thanks
However, I am not familiar with the events thing you mentioned. I am
looking to
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4
Can this be done through the events, or some other way
Thank you so very much for your help
:
Hi AjgoodsonRight click on the sheet tab and choose "View Code"Paste this
Hi Ajgoodso
Right click on the sheet tab and choose "View Code
Paste this even
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A3"), Target) Is Nothing The
Select Case Target.Valu
Case 1: Range("A4:C4").Value = Range("A8:C8").Valu
Case 2: Range("A4:C4").Value = Range("A9:C9").Valu
End Selec
End I
End Su
Alt q to go back to Exce
Try to change A3 no
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got that to work.
I got that to work. But it will not let me change the numbers 1 and 2 t
words. Can I
:
Re: Excel, Macro, Drop down list
Case "yourword" : Range("A4:C4").Value = Range("A8:C8").Valu
-
Regards Ron de Brui
http://www.rondebruin.nl/tips.htm
YOU ARE GREAT!
YOU ARE GREAT
I have just one more question
What if the ranges are both on different tabs? i.e. tab1 and tab2
Thank you so much!
:
Re: Excel, Macro, Drop down list
No problem if you have the values of
Range("A8:C8") and Range("A9:C9") on another tab
Use this then
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Value
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
Re: Excel, Macro, Drop down list
IT WORKS! IT WORKS!
Thank you again...Have a great day!
:
Building on this
Hi,
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
Hello,I took Ajgoodson and Ron example's below and tried to make a few
Hello,
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
:
Since you are familiar with worksheet events, you may like this better than
Since you are familiar with worksheet events, you may like this better than
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Excel, Macro, Drop down list
I have a question. I want two use two drop down menus one of whose list contents should depend on the selection in the first drop down menu.
For example suppose a1(list1) has 3 cases(1,2,3) if i select case 1, I want a2(list2)'s contents to be
range(a8:a11). If i select case 2, I want a2(list2)'s contents to be range(b8:b11). And finally if i select case 3, I want a2(list2)'s contents to be range(c8:c11).
How do I go about to do that?
Please note that by contents I mean source for list.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Create Hidden SharePoint List for Site Owners
http://www.eggheadcafe.com/tutorial...60-917a85a41b08/create-hidden-sharepoint.aspx
D
Dave Peterson
Set up a table (A1:C8) on a (hidden?) sheet.
Give the first column of that table a nice name.
Then use the first column of this table as the source for Data|Validation.
Then in A2, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,2,false)
And in A3, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,3,false)
Debra Dalgleish has some notes about naming the range:
http://contextures.com/xlDataVal01.html#Name
And for using =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble
Give the first column of that table a nice name.
Then use the first column of this table as the source for Data|Validation.
Then in A2, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,2,false)
And in A3, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,3,false)
Debra Dalgleish has some notes about naming the range:
http://contextures.com/xlDataVal01.html#Name
And for using =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble
H
hennie lategan
hi i have a drop down box with values 1-35
35 represent "" (empty cell)
how can i get a macro command to set the drop down value to "" Nothing
please help
Ajgoodso wrote:
Excel, Macro, Drop down list
14-Mar-07
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Previous Posts In This Thread:
Excel, Macro, Drop down list
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Re: Excel, Macro, Drop down list
Hi Ajgoodso
You can use Data>Validation to create the lis
http://www.contextures.com/xlDataVal01.htm
Then you can use the Change event to do what you wan
http://www.cpearson.com/excel/events.ht
Example for cell A
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A1"), Target) Is Nothing The
'call your macro her
End I
End Su
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got the list created from the data valuation. Thanks!
I got the list created from the data valuation. Thanks
However, I am not familiar with the events thing you mentioned. I am
looking to
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4
Can this be done through the events, or some other way
Thank you so very much for your help
:
Hi AjgoodsonRight click on the sheet tab and choose "View Code"Paste this
Hi Ajgoodso
Right click on the sheet tab and choose "View Code
Paste this even
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A3"), Target) Is Nothing The
Select Case Target.Valu
Case 1: Range("A4:C4").Value = Range("A8:C8").Valu
Case 2: Range("A4:C4").Value = Range("A9:C9").Valu
End Selec
End I
End Su
Alt q to go back to Exce
Try to change A3 no
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got that to work.
I got that to work. But it will not let me change the numbers 1 and 2 t
words. Can I
:
Re: Excel, Macro, Drop down list
Case "yourword" : Range("A4:C4").Value = Range("A8:C8").Valu
-
Regards Ron de Brui
http://www.rondebruin.nl/tips.htm
YOU ARE GREAT!
YOU ARE GREAT
I have just one more question
What if the ranges are both on different tabs? i.e. tab1 and tab2
Thank you so much
:
Re: Excel, Macro, Drop down list
No problem if you have the values o
Range("A8:C8") and Range("A9:C9") on another ta
Use this the
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Valu
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
Re: Excel, Macro, Drop down list
IT WORKS! IT WORKS
Thank you again...Have a great day
:
Building on this
Hi,
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
Hello,I took Ajgoodson and Ron example's below and tried to make a few
Hello,
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
:
Since you are familiar with worksheet events, you may like this better than
Since you are familiar with worksheet events, you may like this better than
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Excel, Macro, Drop down list
I have a question. I want two use two drop down menus one of whose list contents should depend on the selection in the first drop down menu.
For example suppose a1(list1) has 3 cases(1,2,3) if i select case 1, I want a2(list2)'s contents to be
range(a8:a11). If i select case 2, I want a2(list2)'s contents to be range(b8:b11). And finally if i select case 3, I want a2(list2)'s contents to be range(c8:c11).
How do I go about to do that?
Please note that by contents I mean source for list.
Dropdown Lists
I have cell that includes an 8 item dropdown list (via data validation) and I would like to manage 8 scenarios from this list.
For example if I have scenario 1 selected I would like:
12% to appear in cell A1
$1,000 to appear in cell A2
50% to appear in cell A3
Scenario 2 would be three different, but known, values in the same three cells and so forth for the 6 other scenarios.
I realize that this can be done via if statements but I would like to increase the functionality of the scenarios in that, for scenario 1, the base case is 12%, $1,000 and 50% but I'd like to be able to type over, for example the 12%, with 10%.
Then, if I change to scenario 2 the cells update and when I change back to scenario 1, 12%, $1,000 and 50% reappear.
So, if I had used if statements, when typing over the if statement in cell A1 which would yield 12% with a 10% (hard-coded) the if statement would be gone and switching the dropdown list to scenario 1 would not work.
Thank you very much for your help.
Set up a table (A1:C8) on a (hidden?
Set up a table (A1:C8) on a (hidden?) sheet.
Give the first column of that table a nice name.
Then use the first column of this table as the source for Data|Validation.
Then in A2, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,2,false)
And in A3, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,3,false)
Debra Dalgleish has some notes about naming the range:
http://contextures.com/xlDataVal01.html#Name
And for using =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble
Rob, Campbell wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorial...1-c5c717c9b184/making-silverlight-emulat.aspx
35 represent "" (empty cell)
how can i get a macro command to set the drop down value to "" Nothing
please help
Ajgoodso wrote:
Excel, Macro, Drop down list
14-Mar-07
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Previous Posts In This Thread:
Excel, Macro, Drop down list
I have a list that I want to make into a drop down list that when eac
individual item is selected, cause a macro to run. Can anyone assist?
Re: Excel, Macro, Drop down list
Hi Ajgoodso
You can use Data>Validation to create the lis
http://www.contextures.com/xlDataVal01.htm
Then you can use the Change event to do what you wan
http://www.cpearson.com/excel/events.ht
Example for cell A
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A1"), Target) Is Nothing The
'call your macro her
End I
End Su
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got the list created from the data valuation. Thanks!
I got the list created from the data valuation. Thanks
However, I am not familiar with the events thing you mentioned. I am
looking to
if cell A3 (list cell) = 1 then I want it to copy cells A8 through C8 to A4
through C4, if cell A3 (list cell) = 2 then I want it to copy cells A9
through C9 to A4 through C4
Can this be done through the events, or some other way
Thank you so very much for your help
:
Hi AjgoodsonRight click on the sheet tab and choose "View Code"Paste this
Hi Ajgoodso
Right click on the sheet tab and choose "View Code
Paste this even
Private Sub Worksheet_Change(ByVal Target As Range
If Not Application.Intersect(Range("A3"), Target) Is Nothing The
Select Case Target.Valu
Case 1: Range("A4:C4").Value = Range("A8:C8").Valu
Case 2: Range("A4:C4").Value = Range("A9:C9").Valu
End Selec
End I
End Su
Alt q to go back to Exce
Try to change A3 no
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
I got that to work.
I got that to work. But it will not let me change the numbers 1 and 2 t
words. Can I
:
Re: Excel, Macro, Drop down list
Case "yourword" : Range("A4:C4").Value = Range("A8:C8").Valu
-
Regards Ron de Brui
http://www.rondebruin.nl/tips.htm
YOU ARE GREAT!
YOU ARE GREAT
I have just one more question
What if the ranges are both on different tabs? i.e. tab1 and tab2
Thank you so much
:
Re: Excel, Macro, Drop down list
No problem if you have the values o
Range("A8:C8") and Range("A9:C9") on another ta
Use this the
Case "yourword" : Range("A4:C4").Value = Sheets("YourOtherSheet").Range("A8:C8").Valu
--
Regards Ron de Brui
http://www.rondebruin.nl/tips.ht
Re: Excel, Macro, Drop down list
IT WORKS! IT WORKS
Thank you again...Have a great day
:
Building on this
Hi,
I'm trying to take this to the next step, but i cant find any good pages.
What im aiming at is to have a front page (say sheet1) where I get a table from ex. sheet2 that contains happenings in november. If I change the table in sheet 1 I want this to be changed also in sheet2. Can this be done easy, and not with having a event for every cell?
Hello,I took Ajgoodson and Ron example's below and tried to make a few
Hello,
I took Ajgoodson and Ron example's below and tried to make a few
modifications. I would like to have a drop down list that depending on the
option that I choose, I would like to hide or unhide one row. I tried to do
the following, but it didn't work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E12"), Target) Is Nothing Then
Select Case Target.Value
Case Reference1:
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Case Reference2:
Rows("13:13").Select
Selection.EntireRow.Hidden = False
End Select
End If
End Sub
Basically Cell E12 can either be "Reference1" or "Reference2", and I would
like to have column 13 hidden/unhidden.
I would really appreciate if anyone could help me out with this!!
Thank you very much!
Martincito
:
Since you are familiar with worksheet events, you may like this better than
Since you are familiar with worksheet events, you may like this better than
validation. Restrict to e12 if desired.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Excel, Macro, Drop down list
I have a question. I want two use two drop down menus one of whose list contents should depend on the selection in the first drop down menu.
For example suppose a1(list1) has 3 cases(1,2,3) if i select case 1, I want a2(list2)'s contents to be
range(a8:a11). If i select case 2, I want a2(list2)'s contents to be range(b8:b11). And finally if i select case 3, I want a2(list2)'s contents to be range(c8:c11).
How do I go about to do that?
Please note that by contents I mean source for list.
Dropdown Lists
I have cell that includes an 8 item dropdown list (via data validation) and I would like to manage 8 scenarios from this list.
For example if I have scenario 1 selected I would like:
12% to appear in cell A1
$1,000 to appear in cell A2
50% to appear in cell A3
Scenario 2 would be three different, but known, values in the same three cells and so forth for the 6 other scenarios.
I realize that this can be done via if statements but I would like to increase the functionality of the scenarios in that, for scenario 1, the base case is 12%, $1,000 and 50% but I'd like to be able to type over, for example the 12%, with 10%.
Then, if I change to scenario 2 the cells update and when I change back to scenario 1, 12%, $1,000 and 50% reappear.
So, if I had used if statements, when typing over the if statement in cell A1 which would yield 12% with a 10% (hard-coded) the if statement would be gone and switching the dropdown list to scenario 1 would not work.
Thank you very much for your help.
Set up a table (A1:C8) on a (hidden?
Set up a table (A1:C8) on a (hidden?) sheet.
Give the first column of that table a nice name.
Then use the first column of this table as the source for Data|Validation.
Then in A2, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,2,false)
And in A3, you can use a formula like:
=if(a1="","",vlookup(a1,sheet2!a1:c8,3,false)
Debra Dalgleish has some notes about naming the range:
http://contextures.com/xlDataVal01.html#Name
And for using =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble
Rob, Campbell wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorial...1-c5c717c9b184/making-silverlight-emulat.aspx
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.