I am using a macro (by Ron de Bruin ---thanks Ron) which sends a Lotus E
Mail to various people...No problem so far..
But I would like to be able to have a checkbox that will allow me to pick who
to send to, instead of hardcoding the names in as I do at the moment.

My checkbox skills are nil.

could anyone point me in the right direction.

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Hi Francois,

Without seeing your existing code, it is difficult to be specific.

However, the following code illustrates a method of returning
a range from checked CheckBoxes from the Forms controls:

'=============
Public Sub Tester()
Dim CBox As CheckBox
Dim Rng As Range
Dim rCell As Range

For Each CBox In ActiveSheet.CheckBoxes
With CBox
If .Value = xlOn Then
If Rng Is Nothing Then
Set Rng = .TopLeftCell
Else
Set Rng = Union(Rng, .TopLeftCell)
End If
End If
End With
Next CBox
If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
MsgBox rCell.Address(0, 0)
Next rCell
End If
End Sub
'<<=============


---
Regards,
Norman




"Francois via OfficeKB.com" <u18959@uwe wrote in message
news:70fa909835f0e@uwe...
I am using a macro (by Ron de Bruin ---thanks Ron) which sends a Lotus E
Mail to various people...No problem so far..
But I would like to be able to have a checkbox that will allow me to pick
who
to send to, instead of hardcoding the names in as I do at the moment.

My checkbox skills are nil.

could anyone point me in the right direction.

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Find attached a sample code which will check whether checkbox is selected or
not. If selected then get caption of checkbox and store in string.

Sub Chk()
Dim strSender As String

If Sheet1.CheckBox1.Value = True Then
strSender = Sheet1.CheckBox1.Caption
End If
If Sheet1.CheckBox2.Value = True Then
strSender = strSender & ";" & Sheet1.CheckBox2.Caption
End If

End Sub

Later, you can use string "strSender" to send mail

Outmail.to = strSender

I suggest you to used listbox or combobox if you have too many selection.
------------------------------------------------------------------------------------
"Francois via OfficeKB.com" wrote:

I am using a macro (by Ron de Bruin ---thanks Ron) which sends a Lotus E
Mail to various people...No problem so far..
But I would like to be able to have a checkbox that will allow me to pick who
to send to, instead of hardcoding the names in as I do at the moment.

My checkbox skills are nil.

could anyone point me in the right direction.

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Himani wrote:
Find attached a sample code which will check whether checkbox is selected or
not. If selected then get caption of checkbox and store in string.

Sub Chk()
Dim strSender As String

If Sheet1.CheckBox1.Value = True Then
strSender = Sheet1.CheckBox1.Caption
End If
If Sheet1.CheckBox2.Value = True Then
strSender = strSender & ";" & Sheet1.CheckBox2.Caption
End If


Many thanks to you both, I'll give them a try


End Sub

Later, you can use string "strSender" to send mail

Outmail.to = strSender

I suggest you to used listbox or combobox if you have too many selection.
------------------------------------------------------------------------------------

I am using a macro (by Ron de Bruin ---thanks Ron) which sends a Lotus E
Mail to various people...No problem so far..
[quoted text clipped - 6 lines]

Thanks in advance

--
Message posted via http://www.officekb.com