Here's something that may help you get started:
Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim myCell As Range
With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("B3:B10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
Boss wrote:
>
> Its working properly..
> Can we even code in manner which will first insert a checkbox and then
> assign cell to it.
> Thanks a lot for ur help.
> Boss
>
> "Dave Peterson" wrote:
>
> > This only changes the linkedcell:
> >
> > Option Explicit
> > Sub testme()
> > Dim wks As Worksheet
> > Dim iCtr As Long
> > Dim TestCBX As CheckBox
> >
> > Set wks = ActiveSheet
> >
> > For iCtr = 1 To wks.CheckBoxes.Count
> > Set TestCBX = Nothing
> > On Error Resume Next
> > Set TestCBX = wks.CheckBoxes("Check Box " & iCtr)
> > On Error GoTo 0
> >
> > If TestCBX Is Nothing Then
> > MsgBox "Name Error!!!"
> > Exit For
> > Else
> > TestCBX.LinkedCell = wks.Cells(iCtr, "A").Address(external:=True)
> > End If
> > Next iCtr
> >
> > End Sub
> >
> >
> > Boss wrote:
> > >
> > > I have 80 check box in a worksheet.
> > > I wish to assign A1 to check box 1, A2 to check box 2.... so on and so forth.
> > >
> > > I wrote a stupid code, please assit me with a correct one.
> > >
> > > Sub Macro1()
> > >
> > > For i = 1 To 5
> > > ActiveSheet.Shapes("Check Box i").Select
> > > With Selection
> > > .Value = xlOff
> > > .LinkedCell = "Ai"
> > > .Display3DShading = False
> > > End With
> > > Next i
> > >
> > > End Sub
> > >
> > > Thanks a lot for ur help !!
> > > Boss
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
Similar Threads
