option buttons - copy/paste cell link problem

hapless in seattle

New Member
Joined
May 19, 2005
Messages
2
Greetings --

I need to make an Excel file that contains multiple instances of the same data entry form within a single worksheet. The form contains some option buttons. I've made a single instance of the form, with the option buttons linked to cells as I want them to be. But, when I copy and paste the form, the option buttons in the new (pasted) form remain linked to the cells in the old (copied) form, instead of "updating" to new cell references.

I can manually assign cell links for each instance of the form within the worksheet, but is there a faster way? Is there a way to copy and paste my form so that each new instance will have its option buttons linked to new corresponding cells, like the way it does when copying & pasting formulas?

THANKS in advance for any help.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
hapless in seattle
Welcome to the Board !!

nobody is taking this one ?
perhaps to difficult ?

use this code on a new sheet to see what it does
can you adapt this to your situation ?

you could use it to put the buttons after copying the form

Code:
Option Explicit

Sub fill_range_with_optionbuttons()
'Erik Van Geit
'050520 0138
Dim rownr As Long, i As Integer
Dim counter As Long
Dim rng As Range
Dim txt() As String
Dim buttoncaption As String
Dim FR As Long, NR As Long, FC As Integer, NC As Integer, SkipRows As Integer

FR = 3
NR = 20
FC = 2
NC = 3

Set rng = Application.InputBox("select the range where you want to put optionbuttons", "SELECT RANGE", Range(Cells(FR, FC), Cells(FR + NR - 1, FC + NC - 1)).Address, Type:=8)
FR = rng(1).Row
FC = rng(1).Column
NR = rng.Rows.Count
NC = rng.Columns.Count

SkipRows = Application.InputBox("how many empty rows do you want between the option buttons?", "SELECT RANGE", 0, Type:=1)

ReDim txt(NC) As String

For i = 1 To NC
txt(i) = InputBox("please provide the caption of the buttons in column " & i, "BUTTON CAPTION", i)
Next i

Application.ScreenUpdating = False

Rows(FR & ":" & FR + NR - 1).RowHeight = 18

For rownr = FR To FR + NR - 1 Step SkipRows + 1
counter = 0
  For i = FC To FC + NC - 1
    counter = counter + 1
    buttoncaption = txt(counter)
    Set rng = Cells(rownr, i)
    If i = FC Then
    ActiveSheet.GroupBoxes.Add(rng.Left, rng.Top, rng.Width * NC, rng.Height).Name = "box" & rownr
    ActiveSheet.Shapes("box" & rownr).OLEFormat.Object.Characters.Text = ""
    End If
    ActiveSheet.OptionButtons.Add(rng.Left, rng.Top, rng.Width, rng.Height).Name = buttoncaption & rownr
    With ActiveSheet.Shapes(buttoncaption & rownr).OLEFormat.Object
    .Characters.Text = buttoncaption
    .LinkedCell = Cells(rownr, FC + NC).Address
    End With
  Next i
Next rownr

Application.ScreenUpdating = False

End Sub

kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,215,324
Messages
6,124,250
Members
449,149
Latest member
mwdbActuary

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top