Linking a Drop Down Cell with the Macro
 

Can anyone advise how I can get a Macro to start when eg "David" is selected
from the drop down cell?

When eg David is selected from the list, I am wanting the Macro to run which
would insert text in another cell eg "January is the Store manager"

For Stewart the text would be altered slightly eg "Stewart is the Assistant
Manager"

Is this possible?

Help!

Thanks.

Linking a Drop Down Cell with the Macro
 

Why use a Macro? If you use Data Validation to create a dropdown list of
names, and next to this use a second column for the positions, then use =a1&"
is the "&a2
where a1 is the drop down cell for Name and a2 is a vlookup returning the
value of the position i.e. =vlookup(a1,rangeoflists,2,false)
Does this help


--
RWS


"white-ryland" wrote:

Can anyone advise how I can get a Macro to start when eg "David" is selected
from the drop down cell?

When eg David is selected from the list, I am wanting the Macro to run which
would insert text in another cell eg "January is the Store manager"

For Stewart the text would be altered slightly eg "Stewart is the Assistant
Manager"

Is this possible?

Help!

Thanks.

Linking a Drop Down Cell with the Macro
 

HI, I have that in place already on the form which the premium side, the
premium is inserted depending on the limit selected.

The example I gave was a very simple one, think this one is better:

I am wanting a different paragraph which appears depending on what is
selected from the list.

It is a form for an insurance quote and when eg they select, "hazardous good"
we have a hazardous Goods Clause" which I want to be inserted on the document.


There are several of these clauses.

Hope you can help?

RWS wrote:
Why use a Macro? If you use Data Validation to create a dropdown list of
names, and next to this use a second column for the positions, then use =a1&"
is the "&a2
where a1 is the drop down cell for Name and a2 is a vlookup returning the
value of the position i.e. =vlookup(a1,rangeoflists,2,false)
Does this help

Can anyone advise how I can get a Macro to start when eg "David" is selected
from the drop down cell?
[quoted text clipped - 10 lines]

Thanks.

Linking a Drop Down Cell with the Macro
 

Does this help:

Type your additional paragraphs in on the form, with all the possible extra
paragraphs in, each seperately in its own cell(s), don't worry that they
overrun the cell
Default is that all the possible paragraphs are visible.

Then use a check box from the forms toolbar, set control properties to say
cell z100.
If checked then it will say TRUE in this cell if selected.
Then use conditional formatting to make the text white if the value is TRUE
(set formula is =z100).Then your text will disappear if the box is ticked.
You can move the box around and call it what you want e.g. Is the shipment
Hazardous? They tick the box and the text appears.

The downside is you will end up with blank lines where your white paragraphs
are, so your form will end up long. Or you could use macro to hide the lines
with the unnecessary text, if for example you want it to print neatly without
big gaps

Sub Hide_rows()
'
' Macro1 Macro
' Macro recorded 23/06/2006 by robs
'Hide rows if check box is ticked
'
If Range("z100") Then
Rows("4:6").Select
Selection.EntireRow.Hidden = True
Else
End If

End Sub

This does it for one paragraph of 3 lines only, you would need to add more

This assumes you do the check box as above (no conditional formatting and
put the link to cell z100.

If the box is not checked the rows will be visible, if checked they are
hidden once the macro is run.

If you do both the above, then the paragraphs will disappear on screen as
soon as the box is checked, but the rows will not hide until the macro is run.

If you put button called print layout on your sheet, and get it to run the
macro above, then should make sure it all fits on a page, by hiding all the
unnecesary blank lines. If you want this could set print range and print it
as well


--
RWS




"white-ryland" wrote:

HI, I have that in place already on the form which the premium side, the
premium is inserted depending on the limit selected.

The example I gave was a very simple one, think this one is better:

I am wanting a different paragraph which appears depending on what is
selected from the list.

It is a form for an insurance quote and when eg they select, "hazardous good"
we have a hazardous Goods Clause" which I want to be inserted on the document.


There are several of these clauses.

Hope you can help?

RWS wrote:
Why use a Macro? If you use Data Validation to create a dropdown list of
names, and next to this use a second column for the positions, then use =a1&"
is the "&a2
where a1 is the drop down cell for Name and a2 is a vlookup returning the
value of the position i.e. =vlookup(a1,rangeoflists,2,false)
Does this help

Can anyone advise how I can get a Macro to start when eg "David" is selected
from the drop down cell?
[quoted text clipped - 10 lines]

Thanks.

Linking a Drop Down Cell with the Macro
 

Hello white-ryland,

Here is a macro I wrote for a similar situation to yours. You may find
this helpful.

First copy this code using CTRL+C then insert a VBA Module into your
Workbook's Project. Paste the code into the new Module. Add the names
of the macros to be called into the Select Case code below each Case
statement. Expand the number of Case statements to match the number of
lines in the DropDown. Be sure to save your changes using CTRL+S.

Link this Macro to the Drop Down. When the user makes a selection, the
macro will determine which line number was selected in the Drop Down.
This line number then determines which macro will be executed.

'Start Code

Sub RunMacrosFromDropDown()
'Get the Name of ComboBox (Drop Down)
Cbo = Application.Caller
'Get the line number of the entry and the entry data
With ActiveSheet.Shapes(Cbo).ControlFormat
CboLine = .ListIndex
CboData = .List(CboLine)
End With
'Select Macro to run based on the Drop Down line selected
Select Case CboLine
Case 1
'Call First Macro
Case 2
'Call Second Macro
Case 3
'Call Third Macro
End Select
End Sub

'End Code


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=554782

Linking a Drop Down Cell with the Macro
 

you need to use the selection change event

in cell A1 create your drop down list
and name the range as "Manager"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Dim MyRange As Range
Set MyRange1 = Range("Manager") ' this is the same named range as the
drop down box with the managers name in


For Each Cell In Target

If Union(Cell, MyRange1).Address = MyRange1.Address Then
Range("C1").FormulaR1C1 = "Test Manager has been changed ..." '
put your own text in here
End If

Next Cell

End Sub


Hope that is what you were after
Mark


--
Loxley
------------------------------------------------------------------------
Loxley's Profile: http://www.excelforum.com/member.php...o&userid=23927
View this thread: http://www.excelforum.com/showthread...hreadid=554782

Linking a Drop Down Cell with the Macro
 

Many thanks for all the responses.

Hi Leith,

I'v pasted this in but am getting error messages in relating to the code you
provided.

Once your code is pasted into the new module, which bits am I actually
changing?

If possible, would you be able to email me your document and i'll see from
there as to what relates to where?

Thanks.

Leith Ross wrote:
Hello white-ryland,

Here is a macro I wrote for a similar situation to yours. You may find
this helpful.

First copy this code using CTRL+C then insert a VBA Module into your
Workbook's Project. Paste the code into the new Module. Add the names
of the macros to be called into the Select Case code below each Case
statement. Expand the number of Case statements to match the number of
lines in the DropDown. Be sure to save your changes using CTRL+S.

Link this Macro to the Drop Down. When the user makes a selection, the
macro will determine which line number was selected in the Drop Down.
This line number then determines which macro will be executed.

'Start Code

Sub RunMacrosFromDropDown()
'Get the Name of ComboBox (Drop Down)
Cbo = Application.Caller
'Get the line number of the entry and the entry data
With ActiveSheet.Shapes(Cbo).ControlFormat
CboLine = .ListIndex
CboData = .List(CboLine)
End With
'Select Macro to run based on the Drop Down line selected
Select Case CboLine
Case 1
'Call First Macro
Case 2
'Call Second Macro
Case 3
'Call Third Macro
End Select
End Sub

'End Code

Sincerely,
Leith Ross


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Linking a Drop Down Cell with the Macro
 

Hello White-Ryland,

I can send you more info on the code later today. You can email me at
and let me know how to contact you.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=554782