Hi All,


I have several checkBoxes in my UserForm.

In My sheet1 column A i have the following

Branch No

100
101
102
102
103
101 ..

the caption of the CheckBox starts with these 3 digit.
for eg. CheckBox1.caption is 100-Head Office and
CheckBox2.caption is 101-Branch1

I want add an X corresponding to each Branch if the
checkBox is selected.

for eg. if CheckBox1 and CheckBox2 are selected then I
want to add X in column B for each 100 and 101 in sheet1

How can I do this using VBA?

TIA
Soniya

Here is a start. (code is untested and may contain typos)

Sub tester1()
Dim rng As Range, cell As Range
Dim sStr As String, lVal As Long
Dim sAddr As String
Dim ctrl As Object


With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MsForms.CheckBox Then
If ctrl.Value Then
sStr = Left(ctrl.Caption, 3)
If IsNumeric(sStr) Then
lVal = CLng(sStr)
Set cell = rng.Find(lVal)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
cell.Offset(0, 1).Value = "X"
Set cell = rng.FindNext(cell)
Loop While cell.Address < sAddr
End If
End If
End If
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"Soniya" wrote in message
...
Hi All,


I have several checkBoxes in my UserForm.

In My sheet1 column A i have the following

Branch No

100
101
102
102
103
101 ..

the caption of the CheckBox starts with these 3 digit.
for eg. CheckBox1.caption is 100-Head Office and
CheckBox2.caption is 101-Branch1

I want add an X corresponding to each Branch if the
checkBox is selected.

for eg. if CheckBox1 and CheckBox2 are selected then I
want to add X in column B for each 100 and 101 in sheet1

How can I do this using VBA?

TIA
Soniya

Soniya,

I use this code to revise cell values but you could use it with a macro with
your selection(s)...

Sub PrepareRows(pSheet As String, pRange As String)
'-----------------------------------------------------------------------------------'
' This routine will delete rows from a spreadsheet pertaining to profit
centers '
' that are not part of the consoldation. Also, converts the profit centers
that '
' are part of the consolidation to the proper consolidation label.
'
'-----------------------------------------------------------------------------------'
Dim book As Workbook
Dim sheet As Worksheet
Dim Range As Range

Dim r As Long
Dim Xcount As Long
Dim Xrange As Range

On Error GoTo EndMacro

Set book = ThisWorkbook
Set Range = book.Worksheets(pSheet).Range(pRange)
Set Xrange = Range.Rows

Xcount = Xrange.Rows.Count + 1

For r = Xrange.Rows.Count To 2 Step -1
If Range.Rows(r).Columns(1) = "Group1" Then
GoTo Skip
ElseIf Range.Rows(r).Columns(1) = "Group2" Then
GoTo Skip
ElseIf Range.Rows(r).Columns(1) = "752999" Then
Range.Rows(r).Columns(1) = "Group1"
ElseIf Range.Rows(r).Columns(1) = "907999" Then
Range.Rows(r).Columns(1) = "Group1"
ElseIf Range.Rows(r).Columns(1) = "902999" Then
Range.Rows(r).Columns(1) = "Group2"
ElseIf Range.Rows(r).Columns(1) = "984999" Then
Range.Rows(r).Columns(1) = "Group2"
Else
Range.Rows(r).EntireRow.Delete
End If
Skip:
Next r

EndMacro:

Set book = Nothing
Set sheet = Nothing
Set Range = Nothing
Set Xrange = Nothing

End Sub

I used named ranges with offsets so that I use the routine for another
worksheets that have the codes in the same column. But it could be revised
to pass which column is to be revised.

HTH, Dean.
"Tom Ogilvy" wrote:

Here is a start. (code is untested and may contain typos)

Sub tester1()
Dim rng As Range, cell As Range
Dim sStr As String, lVal As Long
Dim sAddr As String
Dim ctrl As Object


With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MsForms.CheckBox Then
If ctrl.Value Then
sStr = Left(ctrl.Caption, 3)
If IsNumeric(sStr) Then
lVal = CLng(sStr)
Set cell = rng.Find(lVal)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
cell.Offset(0, 1).Value = "X"
Set cell = rng.FindNext(cell)
Loop While cell.Address < sAddr
End If
End If
End If
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"Soniya" wrote in message
...
Hi All,


I have several checkBoxes in my UserForm.

In My sheet1 column A i have the following

Branch No

100
101
102
102
103
101 ..

the caption of the CheckBox starts with these 3 digit.
for eg. CheckBox1.caption is 100-Head Office and
CheckBox2.caption is 101-Branch1

I want add an X corresponding to each Branch if the
checkBox is selected.

for eg. if CheckBox1 and CheckBox2 are selected then I
want to add X in column B for each 100 and 101 in sheet1

How can I do this using VBA?

TIA
Soniya

For each checkbox object, try the following code and
modify the condition criteria for each of them.

Private Sub CheckBox1_Click()
Range("A1").Select
If CheckBox1.Value = True Then
Do Until ActiveCell = ""
If ActiveCell = "100" Then
ActiveCell.Offset(columnoffset:=1) = "x"
End If
ActiveCell.Offset(rowoffset:=1).Activate
Loop
End If
End Sub

-----Original Message-----
Hi All,


I have several checkBoxes in my UserForm.

In My sheet1 column A i have the following

Branch No

100
101
102
102
103
101 ..

the caption of the CheckBox starts with these 3 digit.
for eg. CheckBox1.caption is 100-Head Office and
CheckBox2.caption is 101-Branch1

I want add an X corresponding to each Branch if the
checkBox is selected.

for eg. if CheckBox1 and CheckBox2 are selected then I
want to add X in column B for each 100 and 101 in sheet1

How can I do this using VBA?

TIA
Soniya

.