+ Reply to Thread
Results 1 to 22 of 22

Option Buttons, copying

  1. #1
    Registered User
    Join Date
    11-20-2006
    Posts
    23

    Option Buttons, copying

    I have a database (of sorts) that I am using to record stock discrepancies. I am using Option Buttons as a way of identifying what is wrong with the stock listed. The idea being that a memebr of staff can just click a selection and not spend much time (or need Excel knowledge to use it) when entering the infomation. I have almost finished building it, but have come up against a problem.

    When I 'drag & drop' the first row to create many many more rows, the Option Button 'cell link' doesn't increment....I have taken off the "$" absolute reference from the link, but still no joy. Is it me? or can't it be done....?

    The problem is, if it can't be done, that I have 9 Option Buttons on each row, and an almost infinate ammount of rows, so of course editing each 'cell link' on each buttn is out of the question.

    Help!

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    I have a database (of sorts) that I am using to record stock discrepancies. I am using Option Buttons as a way of identifying what is wrong with the stock listed. The idea being that a memebr of staff can just click a selection and not spend much time (or need Excel knowledge to use it) when entering the infomation. I have almost finished building it, but have come up against a problem.

    When I 'drag & drop' the first row to create many many more rows, the Option Button 'cell link' doesn't increment....I have taken off the "$" absolute reference from the link, but still no joy. Is it me? or can't it be done....?

    The problem is, if it can't be done, that I have 9 Option Buttons on each row, and an almost infinate ammount of rows, so of course editing each 'cell link' on each buttn is out of the question.

    Help!
    Hi,

    I think that a small sample of your worksheet could be useful for this question, can you copy a couple of rows (including the buttons) to a new sheet and SaveAs a new book, rightmouse that, Add to Archive, as a .zip and post it here.

    9 buttons per row in a stock list will tend to 'add' to a sheet.

    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Hi there,

    Thanks for reply, and sorry for my delayed reply, I had to remove some tables and lists that contained "sensitive" info....anyway here goes...

    Example database.zip

    Dfire
    Last edited by Dfire; 11-27-2006 at 10:32 AM.

  4. #4
    Registered User
    Join Date
    11-20-2006
    Posts
    23

    Can anyone help on this, I still can't get it to work.

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire

    Can anyone help on this, I still can't get it to work.
    hi - something of an ugly response huh, as you say, it doesn't like to adjust itsself, but will on 'Insert Row'

    You may need a macro to create these rows, the macro should be able to set the link cell ok.

    Can you Record a macro, set up a single row, with the buttons etc, and zip that here?

    Cheers
    ---
    Last edited by Bryan Hessey; 12-03-2006 at 05:32 AM.

  6. #6
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Ah, great the 'insert row' I hadn't spotted, but unfortunately it's looking like one step forward and two back.....now I can't get the macro to 'paste' the new row properly.....it pastes it back over the wrong row.

    I can 'paste' when I just walk through the process myself, and as I 'record' it works, but as soon as I run the macro......crash! AAAAHHHHhhhhhhh!!!

    I can't see why it does it either, in Visual Basic the correct row I.D. ie there, but on the sheet it puts it back on top of the copied row.....and with no formatting.....(which I guess maybe because it doesn't finish properly).

    So I'm still begging for help....

    Dfire

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Ah, great the 'insert row' I hadn't spotted, but unfortunately it's looking like one step forward and two back.....now I can't get the macro to 'paste' the new row properly.....it pastes it back over the wrong row.

    I can 'paste' when I just walk through the process myself, and as I 'record' it works, but as soon as I run the macro......crash! AAAAHHHHhhhhhhh!!!

    I can't see why it does it either, in Visual Basic the correct row I.D. ie there, but on the sheet it puts it back on top of the copied row.....and with no formatting.....(which I guess maybe because it doesn't finish properly).

    So I'm still begging for help....

    Dfire
    Hi,

    Can you post that part of the code, it does assist to see what you are actually doing rather than try to visualise from "......crash! AAAAHHHHhhhhhhh!!!" etc.

    ---

  8. #8
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Bryan,

    Forgive my lack of detail, it was late...
    Below is all the VBA script for the recorded macro...
    It crashes at the 'paste' that is in bold...
    ...and always pastes into the wrong row, even though the correct row reference is in the scrip, (row 30).

    VBA is a little beyound me at the moment, so although I can kind of see what it's doing I don't fully understand it....

    One point I have noticed, is that each time I use the macro the Option button numbers in the script change....not sure if that has any relevance

    Dfire


    Sub Insert_line()
    '
    ' Insert_line Macro
    ' Macro recorded 07/12/2006 by Dfire
    '

    '
    Range("A30").Select
    Selection.EntireRow.Insert
    Rows("31:31").Select
    Selection.Copy
    Rows("30:30").Select
    ActiveSheet.OptionButtons.Add(640.5, 196.5, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(720.75, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(801, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(881.25, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(967.5, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1046.25, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1128, 197.25, 24, 17.25).Select
    ActiveSheet.GroupBoxes.Add(620.25, 187.5, 1715.25, 38.25).Select
    ActiveSheet.OptionButtons.Add(1698, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1765.5, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1832.25, 197.25, 24, 17.25).Select
    ActiveSheet.Paste
    Range("M30").Select
    Application.CutCopyMode = False
    ActiveSheet.Shapes("Option Button 319").Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320")). _
    Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324")).Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325")).Select
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327" _
    , "Option Button 328")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327" _
    , "Option Button 328", "Option Button 329")).Select
    With Selection
    .LinkedCell = "L30"
    .Display3DShading = True
    End With
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Range("B30").Select
    End Sub

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Bryan,

    Forgive my lack of detail, it was late...
    Below is all the VBA script for the recorded macro...
    It crashes at the 'paste' that is in bold...
    ...and always pastes into the wrong row, even though the correct row reference is in the scrip, (row 30).

    VBA is a little beyound me at the moment, so although I can kind of see what it's doing I don't fully understand it....

    One point I have noticed, is that each time I use the macro the Option button numbers in the script change....not sure if that has any relevance

    Dfire


    Sub Insert_line()
    '
    ' Insert_line Macro
    ' Macro recorded 07/12/2006 by Dfire
    '

    '
    Range("A30").Select
    Selection.EntireRow.Insert
    Rows("31:31").Select
    Selection.Copy
    Rows("30:30").Select
    ActiveSheet.Paste
    ActiveSheet.OptionButtons.Add(640.5, 196.5, 24, 17.25).Select
    ActiveSheet.Paste
    ActiveSheet.OptionButtons.Add(720.75, 197.25, 24, 17.25).Select
    ActiveSheet.Paste
    ActiveSheet.OptionButtons.Add(801, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(881.25, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(967.5, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1046.25, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1128, 197.25, 24, 17.25).Select
    ActiveSheet.GroupBoxes.Add(620.25, 187.5, 1715.25, 38.25).Select
    ActiveSheet.OptionButtons.Add(1698, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1765.5, 197.25, 24, 17.25).Select
    ActiveSheet.OptionButtons.Add(1832.25, 197.25, 24, 17.25).Select
    ActiveSheet.Paste
    Range("M30").Select
    Application.CutCopyMode = False
    ActiveSheet.Shapes("Option Button 319").Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320")). _
    Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327" _
    , "Option Button 328")).Select
    ActiveSheet.Shapes.Range(Array("Option Button 319", "Option Button 320", _
    "Option Button 321", "Option Button 322" _
    , "Option Button 323", "Option Button 324", "Option Button 325", _
    "Option Button 327" _
    , "Option Button 328", "Option Button 329")).Select
    With Selection
    .LinkedCell = "L30"
    .Display3DShading = True
    End With
    Range("B30").Select
    End Sub
    Hi,

    recorded macros are fun huh,

    you can delete those 'scroll' lines, there is no reason to retain them, it's just where you scrolled whilst recording.

    Not sure if you intended to paste the 'copy' to just the row, or the row plus the buttons, so, to test, can you add the copy immediately after the Row 30 as shown in blue and see if that pastes.

    Let me know how that goes.

    ---

  10. #10
    Registered User
    Join Date
    11-20-2006
    Posts
    23

    Smile Getting somewhere, but now....

    .....I have a new problem....

    Firstly, I've changed the way the macro works slightly, partly based on your last comments...(macro is in the attahced), so thanks so far, you led me in the right direction...

    It now copies, and then 'inserts copied cells'....which retains all the formatting and more importantly puts it all in the correct row...and answers your question "what do I want pasted?"

    But, now the cell links are updating incorrectly....I have attached a newer version of the database and added some comments within... so hopefully they make more sence (in context)....

    If this ever works properly, and I think we're going in the right direction, I will want to run the macro 1,500 times to create that many rows....is there a way of programming the macro to do that.....maybe this bit is for later, once it does work....finger crossed.

    Dfire
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    .....I have a new problem....

    Firstly, I've changed the way the macro works slightly, partly based on your last comments...(macro is in the attahced), so thanks so far, you led me in the right direction...

    It now copies, and then 'inserts copied cells'....which retains all the formatting and more importantly puts it all in the correct row...and answers your question "what do I want pasted?"

    But, now the cell links are updating incorrectly....I have attached a newer version of the database and added some comments within... so hopefully they make more sence (in context)....

    If this ever works properly, and I think we're going in the right direction, I will want to run the macro 1,500 times to create that many rows....is there a way of programming the macro to do that.....maybe this bit is for later, once it does work....finger crossed.

    Dfire
    Hi,

    the second part first, replace
    Rows("30:30").Select

    with
    Dim iRow as long
    for iRow = 30 to 35 ' then as 1500 after testing
    rows(iRow).Select

    for the first part, the row 36 and 30, if you Rightmouse the buttons, you will see that 36 is format to $L$30 and 30 to L35

    you will need to delete these or manually correct them.

    hth
    ---

  12. #12
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Hi Bryan,

    Forgive me for being dim....but in reference to your "for the first part"...isn't that what I said in the attachment, what I can't get it to do is correctly "format control" the cell ref.....the one I inserted and changed whilst recording the macro worked, and at first glance so did the first attempt at running the macro....but for some reason it 'absolutes' the first go and then increments each time after that, but all new subsequent rows increment altogether......as if like a group.

    What I want it to do is, insert the new row with cell ref L30 and only increment the cell refs (in the old rows) as each new row is added, so the new one should always (initially) read L30 and only change from that if/when the next 'insert' is done........I think that makes sence???

    As for the "second part" many thanks, I knew it would be possible but didn't know the coding.....once the cell ref problem is cured I look forward to trying that bit.

    Many thanks for your time so far, please hang on until you've beaten enough sence into me to fix this....

    Dfire

  13. #13
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Hi Bryan,

    Forgive me for being dim....but in reference to your "for the first part"...isn't that what I said in the attachment, what I can't get it to do is correctly "format control" the cell ref.....the one I inserted and changed whilst recording the macro worked, and at first glance so did the first attempt at running the macro....but for some reason it 'absolutes' the first go and then increments each time after that, but all new subsequent rows increment altogether......as if like a group.

    What I want it to do is, insert the new row with cell ref L30 and only increment the cell refs (in the old rows) as each new row is added, so the new one should always (initially) read L30 and only change from that if/when the next 'insert' is done........I think that makes sence???

    As for the "second part" many thanks, I knew it would be possible but didn't know the coding.....once the cell ref problem is cured I look forward to trying that bit.

    Many thanks for your time so far, please hang on until you've beaten enough sence into me to fix this....

    Dfire
    for the first part, did you distinguish between L30 and L$30 ?

  14. #14
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Er, yep, I have tried all variations of '$' and 'no $'...and they all seem to do the same thing....I've even recorded the macro to insert below the original line thinking that the increment of new cells might work in reverse....but no!

    But, I've been thinking, (not always a good thing)...

    Is there a formula that can be typed into the macro that identifies the row itself and then uses column 30 for that row....something like (and forgive me my incompetence at 'coding')

    .LinkedCell = "THISROW,30"

    Then, each time it runs it would identify the row at that stage and not increment.....hopefullly

    Dfire

  15. #15
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Er, yep, I have tried all variations of '$' and 'no $'...and they all seem to do the same thing....I've even recorded the macro to insert below the original line thinking that the increment of new cells might work in reverse....but no!

    But, I've been thinking, (not always a good thing)...

    Is there a formula that can be typed into the macro that identifies the row itself and then uses column 30 for that row....something like (and forgive me my incompetence at 'coding')

    .LinkedCell = "THISROW,30"

    Then, each time it runs it would identify the row at that stage and not increment.....hopefullly

    Dfire
    Hi,

    something is not good with these buttons, if I delete a Row (at varied points) I get a double-up of buttons in a cell, the upper buttomn turns the row green, the lower white, and the column M button 'red'

    the format would bve something like .LinkedCell = "L"& thisrow +30

    ----

  16. #16
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Hi Bryan, still with me then.....

    The formatting is some conditional formatting I've got in the database....and yes when I have deleted rows I've had to go and pick out all the rouge buttons left behind....I've never really worked out why that happens....I just delete the buttons first en-mass and then delete the row....

    As for the formula, I'll add it in and let you know....

    Daryl.

  17. #17
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Hi Bryan, still with me then.....

    The formatting is some conditional formatting I've got in the database....and yes when I have deleted rows I've had to go and pick out all the rouge buttons left behind....I've never really worked out why that happens....I just delete the buttons first en-mass and then delete the row....

    As for the formula, I'll add it in and let you know....

    Daryl.
    Hi,

    that should be good, also might be good to straighten that Macro before using it, you can make a copy into Module 1 or 2 (currently unused) - just rename it, then remove all of the ' lines, then remove any '.select' that is immediately followed by another select, then look where the 'Paste' should be. (note the 'cutcopymode' should follow the last required Paste for the copied item)

    Good luck with the rest . . . .
    ---

  18. #18
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Afternoon Bryan,

    Unfortunately this didn't work either, I have a much shorter neater macro, but it is still linking the wrong cells.....the first row inserted by the macro seems to work, until you insert another row with the macro....then the previous row doesn't increment, (and in fact never does), and the new row picks up the cell-link for the row below the first, (ie the original that the macro copies)??? every subsequent row inserted by the macro then picks up the same cell-link as the previous (and these do increment, but altogether), so you end up with loads of lines all acting together...

    Do you think we've been beaten here, and shall I just build this the slow way....line-by-line???

    If you have any more suggestions...........I would be most greatful....

    Regards,

    Daryl

  19. #19
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Afternoon Bryan,

    Unfortunately this didn't work either, I have a much shorter neater macro, but it is still linking the wrong cells.....the first row inserted by the macro seems to work, until you insert another row with the macro....then the previous row doesn't increment, (and in fact never does), and the new row picks up the cell-link for the row below the first, (ie the original that the macro copies)??? every subsequent row inserted by the macro then picks up the same cell-link as the previous (and these do increment, but altogether), so you end up with loads of lines all acting together...

    Do you think we've been beaten here, and shall I just build this the slow way....line-by-line???

    If you have any more suggestions...........I would be most greatful....

    Regards,

    Daryl
    where's the fun in that

    can you post your nice clean edition?

  20. #20
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Bryan....see attched....

    D....
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Dfire
    Bryan....see attched....

    D....
    Hi,

    See the attached.

    In row 30 there are initially Buttons with no link addresses, but when you move both of the outline shapes you find the buttons are now Row 31 'green' buttons.

    Row 31 also has 2 outline shapees, but these appear to have no effect.

    If you remove row 30, you get the doubled buttons on row 31. If you then remove an outline, you see the doubles are now green for L31.

    Are these outline shapes required? Can we remove them all and re-set the red, green and clear button setting?

    ----
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Morning Bryan,

    Right, sorry about the odd 'extras' in the worksheet....

    I have cleaned it up, (I think) and put some notes in the worksheet explaining the colours and 'group boxes'....

    Hope it makes sence this time....

    One bit I would add, is that when a row is deleted for some reason the option buttons and group box are always left behind......but if you 'insert copied cells' it pastes in everything ok......the best way to remove a row is selecting the option buttons and group box (using the drawing toolbar arrow) and deleting them first, then remove the row....otherwise the buttons left behind start doing odd things in relation to the ones already in the newly moved up row.


    Daryl
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1