Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need help with Error 'object variable or with block variable not set'
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default Need help with Error 'object variable or with block variable not set'
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

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default Need help with Error 'object variable or with block variable not
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


--
Hope that helps.

Vergel Adriano


"Francois via OfficeKB.com" wrote:

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

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default Need help with Error 'object variable or with block variable not
Vergel Adriano wrote:
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,

[quoted text clipped - 118 lines]
'


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


End Sub

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Need help with Error 'object variable or with block variable not set'
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)



"Francois via OfficeKB.com" <u18959@uwe wrote in message
news:717a4d5ed04f0@uwe...
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

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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Need help with Error 'object variable or with block variable not
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" <u18959@uwe wrote in message
news:717a78b2eedbc@uwe...
Vergel Adriano wrote:
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,

[quoted text clipped - 118 lines]
'


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


End Sub

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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default Need help with Error 'object variable or with block variable not set'
Bob Phillips wrote:
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,

[quoted text clipped - 116 lines]
'
End Sub


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

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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime Error '91' Object variable or With block variable not set Alec Coliver Excel Discussion (Misc queries) 2 October 24th 09 02:29 PM
Run-Time error '91': Object variable of With block variable not set jammin1911 Excel Programming 3 June 6th 06 06:36 PM
Getting inconsistent Error 91-Object variable or With block variable not set mfq Excel Programming 0 December 14th 05 06:08 PM
Run-time error '91': "Object variable or With block variable not set Mike[_92_] Excel Programming 2 December 30th 04 10:59 AM
Cells.Find error Object variable or With block variable not set Peter[_21_] Excel Programming 2 May 8th 04 02:15 PM


All times are GMT +1. The time now is 07:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement