Need help with Error 'object variable or with block variable not set'

  • Thread starter Francois via OfficeKB.com
  • Start date
F

Francois via OfficeKB.com

Hi Folks,

This is driving me NUTS !!

I'm trying to modify a script by John Walkenbach which will create a dialog
box of persons to send an E Mail to.

I have the E Mail code ( from Ron de Bruin)

I'm stuck on an Error 91 object variable or with block variable not set
when I try to set the variables needed.

Could anyone point me in the right direction ?

Code below:-

Option Explicit

Private Sub Macro999()
'

Dim TopPos As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CheckBox1 As CheckBox
Dim CheckBox2 As CheckBox
Dim CheckBox3 As CheckBox
Dim CheckBox4 As CheckBox

Dim sendto1 As Object 'IS THIS RIGHT ?????
Dim sendto2 As Object
Dim sendto3 As Object
Dim sendto4 As Object

Application.ScreenUpdating = False


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

' Add the checkboxes
TopPos = 40

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(1).Text = Sheets("EMAILS").Range("C2")
TopPos = TopPos + 13

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(2).Text = Sheets("EMAILS").Range("C3")
TopPos = TopPos + 13

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(3).Text = Sheets("EMAILS").Range("C4")
TopPos = TopPos + 13

PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(4).Text = Sheets("EMAILS").Range("C5")
TopPos = TopPos + 13



' 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 Persons to send the E Mail to"
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


Application.ScreenUpdating = True

' THIS IS THE PART THAT GIVES THE ERROR !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!


'If CheckBox1 is TRUE then Set variable 'sendto1' to
value of Sheet 'EMAILS' Cell 'C2'
If CheckBox1.Value = True Then Set sendto1 = Sheets("EMAILS").
Range("C2").Value

'If CheckBox2 is TRUE then Set variable 'sendto2' to
value of Sheet 'EMAILS' Cell 'C3'
If CheckBox2.Value = True Then Set sendto2 = Sheets("EMAILS").
Range("C3").Value

'ETC
If CheckBox3.Value = True Then Set sendto3 = Sheets("EMAILS").
Range("C4").Value

'ETC
If CheckBox4.Value = True Then Set sendto4 = Sheets("EMAILS").
Range("C5").Value






' Do the E Mail code here (Which I have already Thanks to Ron de Bruin)
'
'
'
'
'
'
'
'
'

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

Guest

Francois,

Try declaring the sendtoX variables as string.

Dim sendto1 As String


Then, when assigning the values, remove the "Set"

If CheckBox1.Value = True Then sendto1 = Sheets("EMAILS").Range("C2").Value
 
F

Francois via OfficeKB.com

Vergel said:
Francois,

Try declaring the sendtoX variables as string.

Dim sendto1 As String

Then, when assigning the values, remove the "Set"

If CheckBox1.Value = True Then sendto1 = Sheets("EMAILS").Range("C2").Value
Hi Folks,
Click to expand...
[quoted text clipped - 118 lines]
'
Click to expand...
Click to expand...


Thanks for the quick reply, but when I try that I still get the same Error

 
B

Bob Phillips

Private Sub Macro999()
'

Dim TopPos As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CheckBox1 As CheckBox
Dim CheckBox2 As CheckBox
Dim CheckBox3 As CheckBox
Dim CheckBox4 As CheckBox

Dim sendto1 As Object 'IS THIS RIGHT ?????
Dim sendto2 As Object
Dim sendto3 As Object
Dim sendto4 As Object

Application.ScreenUpdating = False


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

' Add the checkboxes
TopPos = 40

Set CheckBox1 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox1.Text = Sheets("EMAILS").Range("C2")
TopPos = TopPos + 13

Set CheckBox2 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox2.Text = Sheets("EMAILS").Range("C3")
TopPos = TopPos + 13

Set CheckBox3 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox3.Text = Sheets("EMAILS").Range("C4")
TopPos = TopPos + 13

Set CheckBox4 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox4.Text = Sheets("EMAILS").Range("C5")
TopPos = TopPos + 13

' 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 Persons to send the E Mail to"
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

Application.ScreenUpdating = True
If PrintDlg.Show Then
'If CheckBox1 is TRUE then Set variable 'sendto1' to value of Sheet
'EMAILS' Cell 'C2'
If CheckBox1.Value = True Then Set sendto1 =
Sheets("EMAILS").Range("C2").Value

'If CheckBox2 is TRUE then Set variable 'sendto2' to value of Sheet
'EMAILS' Cell 'C3'
If CheckBox2.Value = True Then Set sendto2 =
Sheets("EMAILS").Range("C3").Value

'ETC
If CheckBox3.Value = True Then Set sendto3 =
Sheets("EMAILS").Range("C4").Value

'ETC
If CheckBox4.Value = True Then Set sendto4 =
Sheets("EMAILS").Range("C5").Value
End If

' Do the E Mail code here (Which I have already Thanks to Ron de Bruin)
'


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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

see my response earlier.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Francois via OfficeKB.com said:
Vergel said:
Francois,

Try declaring the sendtoX variables as string.

Dim sendto1 As String

Then, when assigning the values, remove the "Set"

If CheckBox1.Value = True Then sendto1 =
Sheets("EMAILS").Range("C2").Value
Hi Folks,
Click to expand...
[quoted text clipped - 118 lines]
'
Click to expand...
Click to expand...


Thanks for the quick reply, but when I try that I still get the same Error
Click to expand...
 
F

Francois via OfficeKB.com

Bob said:
Private Sub Macro999()
'

Dim TopPos As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CheckBox1 As CheckBox
Dim CheckBox2 As CheckBox
Dim CheckBox3 As CheckBox
Dim CheckBox4 As CheckBox

Dim sendto1 As Object 'IS THIS RIGHT ?????
Dim sendto2 As Object
Dim sendto3 As Object
Dim sendto4 As Object

Application.ScreenUpdating = False

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

' Add the checkboxes
TopPos = 40

Set CheckBox1 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox1.Text = Sheets("EMAILS").Range("C2")
TopPos = TopPos + 13

Set CheckBox2 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox2.Text = Sheets("EMAILS").Range("C3")
TopPos = TopPos + 13

Set CheckBox3 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox3.Text = Sheets("EMAILS").Range("C4")
TopPos = TopPos + 13

Set CheckBox4 = PrintDlg.CheckBoxes.Add(78, TopPos, 150, 16.5)
CheckBox4.Text = Sheets("EMAILS").Range("C5")
TopPos = TopPos + 13

' 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 Persons to send the E Mail to"
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

Application.ScreenUpdating = True
If PrintDlg.Show Then
'If CheckBox1 is TRUE then Set variable 'sendto1' to value of Sheet
'EMAILS' Cell 'C2'
If CheckBox1.Value = True Then Set sendto1 =
Sheets("EMAILS").Range("C2").Value

'If CheckBox2 is TRUE then Set variable 'sendto2' to value of Sheet
'EMAILS' Cell 'C3'
If CheckBox2.Value = True Then Set sendto2 =
Sheets("EMAILS").Range("C3").Value

'ETC
If CheckBox3.Value = True Then Set sendto3 =
Sheets("EMAILS").Range("C4").Value

'ETC
If CheckBox4.Value = True Then Set sendto4 =
Sheets("EMAILS").Range("C5").Value
End If

' Do the E Mail code here (Which I have already Thanks to Ron de Bruin)
'

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
'
End Sub
Hi Folks,
Click to expand...
[quoted text clipped - 116 lines]
'
End Sub
Click to expand...
Click to expand...


Thanks Bob, I thought you'd be not far away.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email selected sheets 5
Select Sheets to Print 11
Losing my array from one sub to another 5
Resize msgbox windows 2
How do I add the ability to select the number of printed pages to aDialog Sheet 2
msg for ron 3
Change looping process to ignore certain sheets 6
how can I add a print dialogue? 2

Top