Macro
 

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

Macro
 

??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"borjan" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?

Macro
 

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?

Macro
 

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.

Macro
 

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.

Macro
 

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.

Macro
 

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.

Macro
 

Sorry, not working!

"Gord Dibben" wrote:

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.


.

Macro
 

Please explain "not working"

Is your shape named "AutoShape 1"

Did you paste the code into the worksheet module?


Gord

On Tue, 2 Feb 2010 00:24:01 -0800, borjan
wrote:

Sorry, not working!

"Gord Dibben" wrote:

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.


.

Macro
 

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" wrote:

Please explain "not working"

Is your shape named "AutoShape 1"

Did you paste the code into the worksheet module?


Gord

On Tue, 2 Feb 2010 00:24:01 -0800, borjan
wrote:

Sorry, not working!

"Gord Dibben" wrote:

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.


.


.

Macro
 

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

On Tue, 2 Feb 2010 14:13:01 -0800, borjan
wrote:

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" wrote:

Please explain "not working"

Is your shape named "AutoShape 1"

Did you paste the code into the worksheet module?


Gord

On Tue, 2 Feb 2010 00:24:01 -0800, borjan
wrote:

Sorry, not working!

"Gord Dibben" wrote:

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.


.


.

Macro
 

Or you can send your workbook to me.

Gord

On Tue, 02 Feb 2010 14:53:09 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Tue, 2 Feb 2010 14:13:01 -0800, borjan
wrote:

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" wrote:

Please explain "not working"

Is your shape named "AutoShape 1"

Did you paste the code into the worksheet module?


Gord

On Tue, 2 Feb 2010 00:24:01 -0800, borjan
wrote:

Sorry, not working!

"Gord Dibben" wrote:

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

On Thu, 28 Jan 2010 13:16:12 -0800, borjan
wrote:

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" wrote:

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 DataListCreate 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


On Thu, 28 Jan 2010 09:31:02 -0800, borjan
wrote:

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.

"L. Howard Kittle" wrote:

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" wrote in message
...
Recording a macro from a list is ok, but
how to assign the list to the macro?



.


.


.


.