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