Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
I am copying a spin button several times (over 100 spin buttons in this input
spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
F5SpecialObjectsOK
With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Ok, but I wanted each spinner to have a different cell reference??? In other
words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
I don't know how to change the linked cell for each spinner currently on a
sheet. Here is code to create 25 spinners down column B with incrementing linked cell reference. Maybe that can help? Sub add_spinner() With ActiveSheet For i = 1 To 25 Set cb = .Shapes.AddFormControl(xlSpinner, 70, i * 20, 15, 15) cb.ControlFormat.LinkedCell = "A" & i Next End With End Sub Gord On Wed, 26 Aug 2009 13:50:05 -0700, Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Are these spinners from the Forms toolbar?
If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
ps. I've found that when I have lots and lots of controls, it's better to use
the vanilla controls from the Forms toolbar. They seem to have less of an impact on excel--and behave much better than the controls from the Control toolbox toolbar. Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Dave
I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
I should have read the thread more closely.
If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Thanks Dave
I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Folks: First, thanks for helping me..I haven't used the code yet but am
grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
So you want to touch all of the spinners and make the linked cell 9 cells to the
right? Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = .TopLeftCell.offset(0, 9).Address(external:=True) End With Next mySpinner End Sub Adam Ronalds wrote: Folks: First, thanks for helping me..I haven't used the code yet but am grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
BTW, I'm not VBA lingual so, please help explain how I would run the program,
thanks "Adam Ronalds" wrote: Folks: First, thanks for helping me..I haven't used the code yet but am grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Adam Ronalds wrote: BTW, I'm not VBA lingual so, please help explain how I would run the program, thanks "Adam Ronalds" wrote: Folks: First, thanks for helping me..I haven't used the code yet but am grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
yup
"Dave Peterson" wrote: So you want to touch all of the spinners and make the linked cell 9 cells to the right? Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = .TopLeftCell.offset(0, 9).Address(external:=True) End With Next mySpinner End Sub Adam Ronalds wrote: Folks: First, thanks for helping me..I haven't used the code yet but am grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Cell Link on Spin Button Form Control
Did it work?
Adam Ronalds wrote: yup "Dave Peterson" wrote: So you want to touch all of the spinners and make the linked cell 9 cells to the right? Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = .TopLeftCell.offset(0, 9).Address(external:=True) End With Next mySpinner End Sub Adam Ronalds wrote: Folks: First, thanks for helping me..I haven't used the code yet but am grateful for your thoughts. Second, a little bit of parameters on what I'm facing. I have 32 groups of 40 spinners (or 2,560 spinners). I copied the first set of 40 spinners 32 times. They are in Columns H:K and reference from cells to the right in columns Q:T. I pulled the spinners from the Forms toolbox and they are all named in no particular order. I hope this helps, please let me know if you have a simple string I can write to get all of these puppies referenced accordingly. Thanks! Adam "Gord Dibben" wrote: Thanks Dave I will play with this in the meantime. Gord On Wed, 26 Aug 2009 19:39:21 -0500, Dave Peterson wrote: I should have read the thread more closely. If every spinner that should be modified is in row 1-25 (and all those in rows 1-25 should be touched), then something like: For Each mySpinner In wks.Spinners With mySpinner if .topleftcell.row 25 then 'do nothing else .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) end if End With Next mySpinner Same kind of thing if the names were nice--Spinner_### (say). I think it's time to wait to see if the OP can help define how to classify his spinners (and which toolbar they came from). Gord Dibben wrote: Dave I thought of this also but OP has over 100 spinners and just wants to change 25 of them. Any way to single out those 25 spinners from a group of 100 on the sheet? Other than by name, that is? If OP has simply copied them as he states, the names may not be in any particular sequence. Gord On Wed, 26 Aug 2009 18:22:32 -0500, Dave Peterson wrote: Are these spinners from the Forms toolbar? If yes (and you've located the spinners within the row like you said you did!): Option Explicit Sub testme() Dim mySpinner As Spinner Dim wks As Worksheet Set wks = ActiveSheet For Each mySpinner In wks.Spinners With mySpinner .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With Next mySpinner End Sub If they're spin buttons from the Control toolbox toolbar: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet Set wks = ActiveSheet For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.SpinButton Then With OLEObj .LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _ .Address(external:=True) End With End If Next OLEObj End Sub Adam Ronalds wrote: Ok, but I wanted each spinner to have a different cell reference??? In other words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell B1 into cells B2:B25 and have the cell reference change for the copied spinners so that I don't have to go into each and change the individual references? "Gord Dibben" wrote: F5SpecialObjectsOK With all spinners selected right-click on one of them and change the linked cell. Will be done to all. Gord Dibben MS Excel MVP On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam wrote: I am copying a spin button several times (over 100 spin buttons in this input spreadsheet) and rather than having to go into the "format control" screen 100+ times, I was hoping that there was a way to quickly and easily change the cell link for each of the copies spin buttons. I removed the anchors in the "cell link" filed of the format control however, when I copy and paste the sin button, this cell link character doesn't change. Is there an quicker way to change the "cell link" for each copied spin button? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form Control Button Color | Excel Discussion (Misc queries) | |||
Excel spin box - no Control tab in Format Control dialong box | Excel Worksheet Functions | |||
Customizing Form Control Button in 2007 | Excel Discussion (Misc queries) | |||
Changing the Spin Button Interval | Excel Discussion (Misc queries) | |||
Form Button - Changing Text | Excel Discussion (Misc queries) |