Excel VBA - Capturing a checkbox caption into a worksheet.

  • Thread starter Thread starter rott
  • Start date Start date
R

rott

Hi
This is what I am trying to do. I have about 170 checkboxes on
worksheet. What I want to happen is when I select certain checkboxes
then click a command button, it will pull the caption off eac
selected checkbox and will populate it into another worksheet in colum
A starting on line 3 in a list format.

Can anyone think of a way this can be done?

Any help would be greatly appreciated because I am at a loss and I a
reaching a deadline.

thanks
rot
 
There are 2 different types of checkboxes that you can have on a worksheet--one
is from the Forms toolbar and the other is from the Control toolbox toolbar:

Option Explicit
Sub testme()

Dim CBX As CheckBox 'from the Forms toolbar
Dim oRow As Long

oRow = 2
For Each CBX In Worksheets("sheet1").CheckBoxes
If CBX.Value = xlOn Then
oRow = oRow + 1
Worksheets("sheet2").Cells(oRow, "A").Value _
= CBX.Caption
End If
Next CBX
End Sub


Sub testme2()

Dim OLEObj As OLEObject
Dim oRow As Long

oRow = 2
For Each OLEObj In Worksheets("Sheet1").OLEObjects
If TypeOf OLEObj.Object Is msforms.CheckBox Then
If OLEObj.Object.Value = True Then
oRow = oRow + 1
Worksheets("sheet2").Cells(oRow, "A").Value _
= OLEObj.Object.Caption
End If
End If
Next OLEObj
End Sub
 
Hi Dave Peterson & rott

I want some help in Excel 2007... I have a big list of check boxes like you had as you said in this form. I want to get items in the check box on a seperate sheet if checked (along with a serial number. Actually I want to select items from the big list to make an order, and then to print that formatted like an order sheet. If possible, please get me a small sample/demo sheet for the same.

Thank you very much in advance.

Sohail
 
Hi Dave Peterson & rott

I want some help in Excel 2007... I have a big list of check boxes like you had as you said in this form. I want to get items in the check box on a seperate sheet if checked (along with a serial number. Actually I want to select items from the big list to make an order, and then to print that formatted like an order sheet. If possible, please get me a small sample/demo sheet for the same.

Thank you very much in advance.

Sohail





Dave Peterson said:
There are 2 different types of checkboxes that you can have on a worksheet--one
is from the Forms toolbar and the other is from the Control toolbox toolbar:

Option Explicit
Sub testme()

Dim CBX As CheckBox 'from the Forms toolbar
Dim oRow As Long

oRow = 2
For Each CBX In Worksheets("sheet1").CheckBoxes
If CBX.Value = xlOn Then
oRow = oRow + 1
Worksheets("sheet2").Cells(oRow, "A").Value _
= CBX.Caption
End If
Next CBX
End Sub


Sub testme2()

Dim OLEObj As OLEObject
Dim oRow As Long

oRow = 2
For Each OLEObj In Worksheets("Sheet1").OLEObjects
If TypeOf OLEObj.Object Is msforms.CheckBox Then
If OLEObj.Object.Value = True Then
oRow = oRow + 1
Worksheets("sheet2").Cells(oRow, "A").Value _
= OLEObj.Object.Caption
End If
End If
Next OLEObj
End Sub


"rott <" wrote:
>
> Hi
> This is what I am trying to do. I have about 170 checkboxes on a
> worksheet. What I want to happen is when I select certain checkboxes,
> then click a command button, it will pull the caption off each
> selected checkbox and will populate it into another worksheet in column
> A starting on line 3 in a list format.
>
> Can anyone think of a way this can be done?
>
> Any help would be greatly appreciated because I am at a loss and I am
> reaching a deadline.
>
> thanks
> rott
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
(e-mail address removed)
Click to expand...
 
Back
Top