Prev: Remove Duplicate Numbers in One Cell
Next: search text files for a certain word
From: JayDe on 16 Jan 2010 08:42
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
From: Gary''s Student on 16 Jan 2010 09:02
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
From: JayDe on 16 Jan 2010 10:22
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
From: Gary''s Student on 16 Jan 2010 10:59
Then see:

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/bed4f8ea69616ad7/47106802f1803eb5?lnk=gst&q=%22insert+checkbox%22#47106802f1803eb5

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
From: Gord Dibben on 16 Jan 2010 15:59
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 <JayDe(a)discussions.microsoft.com>
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

 |  Next  |  Last
Pages: 1 2
Prev: Remove Duplicate Numbers in One Cell
Next: search text files for a certain word