Option Buttons, copying

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.

---

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

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

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.

---

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.

---

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

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 ?

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

----

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

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?

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?

----