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

Try this.

For i = 1 To 5
ActiveSheet.Shapes("Check Box" & i).Select
With Selection
.Value = xlOff
.LinkedCell = "A" & i
.Display3DShading = False
End With
Next i

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

Gettign error.
Boss

"JLGWhiz" wrote:

Try this.

For i = 1 To 5
ActiveSheet.Shapes("Check Box" & i).Select
With Selection
.Value = xlOff
.LinkedCell = "A" & i
.Display3DShading = False
End With
Next i

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

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

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

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

This was Awesome..
You made my day.. !!

Thanks Thanks Thanks a lot.
Boss


"Dave Peterson" wrote:

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