B
borjan
Recording a macro from a list is ok, but
how to assign the list to the macro?
how to assign the list to the macro?
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.
D
Don Guillett
??
L
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
how to assign the list to the macro.
Regards,
Howard
B
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.
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.
G
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
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
B
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)
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)
G
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
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
B
borjan
Sorry, not working!
Gord Dibben said: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
.
G
Gord Dibben
Please explain "not working"
Is your shape named "AutoShape 1"
Did you paste the code into the worksheet module?
Gord
Is your shape named "AutoShape 1"
Did you paste the code into the worksheet module?
Gord
B
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
text in the Shape, the 'Set Vals'=nothing and the Rtext="", but the Shape
gets focus
G
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
Either in 2007 or pre-2007.
Email me at gorddibbATshawDOTca
Change the obvious.
Gord
G
Gord Dibben
Or you can send your workbook to me.
Gord
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