I have three worksheets in a workbook; using a userform to administer them I
would like to email independantly a sheet (this I understand using SendMail)
BUT I would like the VBA code to look at which radio button has focus and
email the correct sheet.

Any help appreciated, I am essentially a newbie with only basic skills...

Matt

matt -

use the control toolbox to add the radio button (also called an option
button) on each sheet, it will initially be set as false & will be
entitled "option button 1".
if you double-click on it, it will bring you to something like

Option Explicit

Private Sub OptionButton1_Click()

End Sub

this private sub is in the WORKSHEET module. then you can write your
e-mail sending program in a regular module, say

public sub send_email_now()

blah blah blah (i know NOTHING about e-mail coding!)

end sub


then in the sheet module private sub you would add:


Option Explicit

Private Sub OptionButton1_Click()

Call send_email_now

set optionbutton1.value=false

End Sub

the option button is connected to THAT specific sheet. so whatever
sheet is triggering the option button will send only that sheet (i'm
pretty sure).
then you have to make sure you unselect the option button.

do this for each worksheet......... you will have 3 private sheet
codes & one public regular module code. each sheet code will call the
send_email_now code.
when somebody clicks on the option button, they will HAVE to have that
worksheet open, so you can use activesheet.

hope this helps!
susan




On Mar 20, 8:10 am, Matt T <Matt
wrote:
I have three worksheets in a workbook; using a userform to administer them I
would like to email independantly a sheet (this I understand using SendMail)
BUT I would like the VBA code to look at which radio button has focus and
email the correct sheet.

Any help appreciated, I am essentially a newbie with only basic skills...

Matt

Susan,

thanks for the help. I'm not sure whether I explained correctly.

The option buttons are on a userform with a single email cmd button also.

What I am trying to do is : through selecting an option button then clicking
the email cmd button it will send the sheet associated with the option button
to recipient.

option 1
option 2
option 3

cmdEmail

if option 1
send sheet 1
or if option 2
send sheet 2
or if option 3
send sheet 3

"Susan" wrote:

matt.

On Mar 20, 7:10 am, Matt T <Matt
wrote:
I have three worksheets in a workbook; using a userform to administer them I
would like to email independantly a sheet (this I understand using SendMail)
BUT I would like the VBA code to look at which radio button has focus and
email the correct sheet.

Any help appreciated, I am essentially a newbie with only basic skills...

Matt

Taken from http://www.j-walk.com/ss/excel/tips/tip48.htm
and modified to fit...

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim ob As OptionButton
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the Option Buttons
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) < 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select Sheet to Email"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount < 0 Then
If PrintDlg.Show Then
For Each ob In PrintDlg.OptionButtons
If ob.Value = xlOn Then
Worksheets(ob.Caption).Copy

' Email code here
Application.Dialogs(xlDialogSendMail).Show
End If
Next ob
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
' CurrentSheet.Activate
End Sub

sorry - i missed the userform part.
sheesh.
susan

You will have to excuse my 'beginnerness' but I can't see how to have the
cmdEmail button look at which option Button is selected.

Also how do I tie a specific sheet to each of the option buttons.

my programming ability is very basic, but I generally learn from playing
with code, if it is either made obvious where I need to go or with lots of
comments.

Matt

matt -
i would handle it differently than okrob, who is a better programmer
than i but who hasn't responded back........... :) but i can help you
with the option buttons being connected to each sheet............
(now, remember i know nothing about the actual e-mailing code!)

in the userform, select all 3 option buttons, & in the properties
window add a group name for all of them (like OptEmail). make each
option button's caption THE EXACT SAME NAME as your 3 worksheets. it
doesn't matter what they are, as long as they match exactly.

sub cmdEmail_click()

dim ocontrol as control
dim Sheet1, Sheet2, Sheet3 as worksheet
dim sCaption as string

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

If Me.oControl.GroupName = "EmailOpt" Then
If oControl.Value = True Then
'it will only choose the one that is selected
sCaption = oControl.Caption
Worksheet(sCaption).select
end if
end if

'PUT YOUR E-MAIL CODE HERE
'using the active (selected) sheet
'(which should now be the
'correct sheet since the option
'button name matches the worksheet name.

unload me
exit sub

i didn't test this - you might have to change around the
If Me.oControl.GroupName line.........
since it's in the userform code you might not need
the "me" designation.

i'm going to go try it out now for myself - i hope it
works for you!
susan








On Mar 20, 1:48 pm, Matt T wrote:
You will have to excuse my 'beginnerness' but I can't see how to have the
cmdEmail button look at which option Button is selected.

Also how do I tie a specific sheet to each of the option buttons.

my programming ability is very basic, but I generally learn from playing
with code, if it is either made obvious where I need to go or with lots of
comments.

Matt

a few minor glitches now fixed in code below (worked great for me!):
(of course i didn't e-mail the sheet, i just printed it for my
testing).

Option Explicit

Sub userform_initialize()
Dim oControl As Control

For Each oControl In Me.Controls
oControl.Value = False
Next oControl

End Sub



Sub cmdEmail_click()


Dim oControl As Control
Dim (worksheetname) As Worksheet
Dim (worksheetname) As Worksheet
Dim (worksheetname) As Worksheet
Dim sCaption As String

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

For Each oControl In Me.Controls
If TypeName(oControl) = "OptionButton" Then
If oControl.GroupName = "EmailOpt" Then
If oControl.Value = True Then
'it will only choose the one that is selected
sCaption = oControl.Caption
ActiveWorkbook.Worksheets(sCaption).Select
End If
End If
End If
Next oControl

'PUT YOUR E-MAIL CODE HERE
'using the active (selected) sheet
'(which should now be the
'correct sheet since the option
'button name matches the worksheet name.

Unload Me

End Sub

hope it works for you!
susan



On Mar 21, 8:24 am, "Susan" wrote:
matt -
i would handle it differently than okrob, who is a better programmer
than i but who hasn't responded back........... :) but i can help you
with the option buttons being connected to each sheet............
(now, remember i know nothing about the actual e-mailing code!)

in the userform, select all 3 option buttons, & in the properties
window add a group name for all of them (like OptEmail). make each
option button's caption THE EXACT SAME NAME as your 3 worksheets. it
doesn't matter what they are, as long as they match exactly.

sub cmdEmail_click()

dim ocontrol as control
dim Sheet1, Sheet2, Sheet3 as worksheet
dim sCaption as string

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

If Me.oControl.GroupName = "EmailOpt" Then
If oControl.Value = True Then
'it will only choose the one that is selected
sCaption = oControl.Caption
Worksheet(sCaption).select
end if
end if

'PUT YOUR E-MAIL CODE HERE
'using the active (selected) sheet
'(which should now be the
'correct sheet since the option
'button name matches the worksheet name.

unload me
exit sub

i didn't test this - you might have to change around the
If Me.oControl.GroupName line.........
since it's in the userform code you might not need
the "me" designation.

i'm going to go try it out now for myself - i hope it
works for you!
susan

On Mar 20, 1:48 pm, Matt T wrote:



You will have to excuse my 'beginnerness' but I can't see how to have the
cmdEmail button look at which option Button is selected.

Also how do I tie a specific sheet to each of the option buttons.

my programming ability is very basic, but I generally learn from playing
with code, if it is either made obvious where I need to go or with lots of
comments.

Matt- Hide quoted text -

- Show quoted text -

Susan,

Thankyou for your help, firstly.

I had found a way for success, but your code is much more beautiful and
consise than mine. I will try your code and tweak where needed.

Once again thanks, Matt.

Susan,

I get a Runtime error :438

Do I need to change the Dim (worksheetname) as WorkSheet to reflect the
actual names of each sheet?

Matt.

yes.........
sorry, i thought that was self-explanatory.
take out the parentheses, as in

Dim Sheet1 as Worksheet

susan


On Mar 21, 10:03 am, Matt T wrote:
Susan,

I get a Runtime error :438

Do I need to change the Dim (worksheetname) as WorkSheet to reflect the
actual names of each sheet?

Matt.

many thanks
Matt.

p.s. told you I was a newbie, know when it its wrong - can't always see the
answer!

"Susan" wrote:

yes.........
sorry, i thought that was self-explanatory.
take out the parentheses, as in

Dim Sheet1 as Worksheet

susan


On Mar 21, 10:03 am, Matt T wrote:
Susan,

I get a Runtime error :438

Do I need to change the Dim (worksheetname) as WorkSheet to reflect the
actual names of each sheet?

Matt.

yes, you're right.......
:D
does it work now????
susan

On Mar 21, 11:05 am, Matt T wrote:
many thanks
Matt.

p.s. told you I was a newbie, know when it its wrong - can't always see the
answer!