Macro

[borjan]

Recording a macro from a list is ok, but
how to assign the list to the macro?

 

[Don Guillett]

??

 

[L. Howard Kittle]

Tell me how you record a macro from a list and I might understand what or
how to assign the list to the macro.

Regards,
Howard

 

[borjan]

When recording a macro from a list You'l get 'Range "the cell".select', same
as a shape; 'shape.select'. But with the shape you can assign the shape to
the macro with the right hand mouseclick, that option is not present with the
right click on a list.

 

[Gord Dibben]

Still not clear what a "list" is from your description.

What version of Excel are you running that has a "list"?

Is your "list" a list created from Data>List>Create list in Excel 2003?

You want to run a macro when you click on a cell or group of cells?


Gord Dibben MS Excel MVP

 

[borjan]

list made from data validation, get the macro to run when selecting a value
in the list. the resault from the value selected in the list is ment to be
the text in a shape.
every time a new value is selected in the list the shapes-text changes with
the value generated from the list select. vers.(x-2007)

 

[Gord Dibben]

Now we're getting clearer.........I think<g>

This worksheet event code will change the text in a shape when choice is
made from a DV dropdown.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim Rtext As String

Set R = Me.Range("A1") 'DV dropdown cell address
Set Vals = Me.Range("M1:M10")

'vals is source range for DV dropdown list and is Vlookup table range

If Intersect(Target, R) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Rtext = Application.VLookup(R.Value, Vals, 1, False)
ActiveSheet.Shapes("AutoShape 1").Select
With Selection.Characters
.Text = Rtext
.Font.Size = 16 ' adjust or delete
End With

SendKeys "{ESC}"
endit:
Application.EnableEvents = True
End Sub

Paste into worksheet module.


Gord

 

[borjan]

Sorry, not working!

Now we're getting clearer.........I think<g>

This worksheet event code will change the text in a shape when choice is
made from a DV dropdown.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim Rtext As String

Set R = Me.Range("A1") 'DV dropdown cell address
Set Vals = Me.Range("M1:M10")

'vals is source range for DV dropdown list and is Vlookup table range

If Intersect(Target, R) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Rtext = Application.VLookup(R.Value, Vals, 1, False)
ActiveSheet.Shapes("AutoShape 1").Select
With Selection.Characters
.Text = Rtext
.Font.Size = 16 ' adjust or delete
End With

SendKeys "{ESC}"
endit:
Application.EnableEvents = True
End Sub

Paste into worksheet module.


Gord



.

 

[Gord Dibben]

Please explain "not working"

Is your shape named "AutoShape 1"

Did you paste the code into the worksheet module?


Gord

 

[borjan]

When selecting from the list, the Shape (named AutoShape 1) does not update the
text in the Shape, the 'Set Vals'=nothing and the Rtext="", but the Shape
gets focus

 

[Gord Dibben]

If you want I can send you a sample workbook that works.

Either in 2007 or pre-2007.

Email me at gorddibbATshawDOTca

Change the obvious.


Gord

 

[Gord Dibben]

Or you can send your workbook to me.

Gord

If you want I can send you a sample workbook that works.

Either in 2007 or pre-2007.

Email me at gorddibbATshawDOTca

Change the obvious.


Gord