Insert a square in a cell with VBA
 

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

Insert a square in a cell with VBA
 

Just put a border around the cell:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/16/2010 by James Ravenswood
'

'
Range("E8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End Sub

--
Gary''s Student - gsnu200909


"JayDe" wrote:

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

Insert a square in a cell with VBA
 

Thank you for the answer.
But a border does not look at all like a checkbox, so I am not very happy
with that solution

JayDe

"Gary''s Student" wrote:

Just put a border around the cell:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/16/2010 by James Ravenswood
'

'
Range("E8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End Sub

--
Gary''s Student - gsnu200909


"JayDe" wrote:

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

Insert a square in a cell with VBA
 

Then see:

http://groups.google.com/group/micro...106802f1803eb5

The Forms checkbox looks like a checkbox, it prints like a checkbox and you
can even check it in Excel.
--
Gary''s Student - gsnu200909


"JayDe" wrote:

Thank you for the answer.
But a border does not look at all like a checkbox, so I am not very happy
with that solution

JayDe

"Gary''s Student" wrote:

Just put a border around the cell:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/16/2010 by James Ravenswood
'

'
Range("E8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End Sub

--
Gary''s Student - gsnu200909


"JayDe" wrote:

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

Insert a square in a cell with VBA
 

Expanding upon the macro that Dave Peterson wrote at the URL you were
pointed to................

Select the range of cells to the right of your list.

List of items in A1:A20

Select B1:B20 and run the macro.

Sub testme()
Dim mycell As Range
For Each mycell In Selection
On Error Resume Next
ActiveSheet.CheckBoxes("cb_" & mycell.Address(False, False)).Delete
On Error GoTo 0
With mycell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
.Name = "cb_" & mycell.Address(False, False)
.Caption = "Agree"
End With
End With
Next
End Sub


Gord Dibben MS Excel MVP

On Sat, 16 Jan 2010 05:42:01 -0800, JayDe
wrote:

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

Insert a square in a cell with VBA
 

Thank you guy's

This did the trick


JayDe



"Gord Dibben" wrote:

Expanding upon the macro that Dave Peterson wrote at the URL you were
pointed to................

Select the range of cells to the right of your list.

List of items in A1:A20

Select B1:B20 and run the macro.

Sub testme()
Dim mycell As Range
For Each mycell In Selection
On Error Resume Next
ActiveSheet.CheckBoxes("cb_" & mycell.Address(False, False)).Delete
On Error GoTo 0
With mycell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
.Name = "cb_" & mycell.Address(False, False)
.Caption = "Agree"
End With
End With
Next
End Sub


Gord Dibben MS Excel MVP

On Sat, 16 Jan 2010 05:42:01 -0800, JayDe
wrote:

Hi

I have a list in Excel 2007. I want to put a square to the right of some of
the list items. How can I do this with VBA.

The end user will see the list on a printout with square/checkbox in the
right column. The idea is that the end user should go thrue the list and
mark/check the items that he agrees on

Regards
JayDe
Norway

.