Message box causing error in code

[Calligra]

This was posted yesterday, but received no responses and it appears to
have been deleted today. I have a piece of code that pulls information
from a worksheet into a userform, displays a messagebox asking the user
if the information in the form is still correct. The code runs fine as
long as the messagebox is not displayed. The code will still run
without error if the No button is selected on the messagebox, the
messagebox closes normally leaving the userform displayed. If the Yes
button is clicked, as soon as the program ends, it displays a "Run-time
error '91' Object variable or With block variable not set". Can anyone
please assist me in solving this error?

Private Sub Workbook_Open()
Application.ScreenUpdating = False

Dim iMinCellRow As Integer
Dim iMaxCellRow As Integer
Dim iCellValue As String
Dim Ctrl As OLEObject
Dim iSheet As Worksheet
Dim iCol As Integer
Dim Counter As Integer

ThisWorkbook.Unprotect Password:="f3rg0t"
ThisWorkbook.Sheets("sheet1").Visible = xlSheetVisible
ThisWorkbook.Sheets("sheet1").Unprotect Password:="f3rg0t"
Application.OnKey "~", "MyTabOrder"

Set iSheet = ThisWorkbook.Sheets("Sheet1")
iMinCellRow = 1
iMaxCellRow = 301
iCol = 1

' Determine which cells on the sheet are available to write to

For Counter = iMinCellRow To iMaxCellRow
iCellValue = iSheet.Cells(Counter, iCol).Value

' Write info to text file.
If (iCellValue = "") Then
iSheet.Cells(Counter, iCol + 1).Value = Now
Exit For
End If
Next Counter

ThisWorkbook.Sheets("sheet1").Protect Password:="f3rg0t"
ThisWorkbook.Sheets("sheet1").Visible = xlSheetHidden

ThisWorkbook.Protect Password:="f3rg0t"

Application.ScreenUpdating = True
With Sheet1
.Activate
.Cells(1, 1).Activate
End With
Load frmLogIn
End Sub

Private Sub cmdCancel_Click()
Dim fValid As Boolean
Dim Ctrl As Control
Dim i As Integer
Dim itemp1 As Integer
Dim itemp2 As Integer
Dim itemp3 As Integer
Dim itemp4 As Integer
Dim itemp5 As Integer

' Read the information from the form and validate
fValid = True
itemp1 = 0
itemp2 = 0
itemp3 = 0
itemp4 = 0
itemp5 = 0

For i = 1 To 31
Set Ctrl = Controls("OptionButton" & i)
If i < 8 Then
If Ctrl.Value = True Then
itemp1 = 1
End If
ElseIf ((i > 7) And (i < 15)) Then
If (((Ctrl.Value = True) And (i <> 14)) Or
((Ctrl.Object.Value = True) And (TextBox3.Value <> ""))) Then
itemp2 = 1
End If
ElseIf ((i > 14) And (i < 22)) Then
If Ctrl.Value = True Then
If i = 21 Then
If OptionButton23.Value = False And
OptionButton24.Value = False Then
itemp3 = 0
Else
itemp3 = 1
End If
Else
itemp3 = 1
End If
End If
ElseIf ((i > 24) And (i < 28)) Then
If Ctrl.Value = True Then
itemp4 = 1
End If
Else
If Ctrl.Value = True Then
itemp5 = 1
End If
End If
Next i

If ((TextBox1.Text = "") Or (TextBox2.Text = "") Or (TxtVersion.Text
= "") Or (TextBox3.Text = "") Or (TextBox4.Text = "") Or (TextBox5.Text
= "") Or (TextBox6.Text = "") Or (itemp1 = 0) Or (itemp2 = 0) Or (itemp3
= 0) Or (itemp4 = 0) Or (itemp5 = 0)) Then
fValid = False
MsgBox "Please complete form prior to exiting module"
Exit Sub
Else
frmLogIn.Hide
Unload frmLogIn
End If
End Sub

Private Sub CmdOK_Click()
Dim TestValue As String
Dim iDateValue As Date
Dim VersionValue As Variant
Dim OtherValue As String
Dim DBVersion As Variant
Dim SerialNum As String
Dim SerialNam As String
Dim fValid As Boolean
Dim Counter As Integer
Dim Ctrl As Control
Dim i As Integer
Dim iCount As Integer
Dim itemp1 As Integer
Dim itemp2 As Integer
Dim itemp3 As Integer
Dim itemp4 As Integer
Dim itemp5 As Integer
Dim Book As Workbook
Dim iMinCellRow As Integer
Dim iMaxCellRow As Integer
Dim iCellValue As String
Dim NameCounter As Integer

'Read the information from the form and validate
fValid = True

itemp1 = 0
itemp2 = 0
itemp3 = 0
itemp4 = 0
itemp5 = 0

For i = 1 To 31
Set Ctrl = Controls("OptionButton" & i)
If i < 8 Then
If Ctrl.Value = True Then
itemp1 = 1
End If
ElseIf ((i > 7) And (i < 15)) Then
If (((Ctrl.Value = True) And (i <> 14)) Or ((Ctrl.Value =
True) And (TextBox3.Value <> ""))) Then
itemp2 = 1
End If
ElseIf ((i > 14) And (i < 22)) Then
If Ctrl.Value = True Then
If i = 21 Then
If OptionButton23.Value = False And
OptionButton24.Value = False Then
itemp3 = 0
Else
itemp3 = 1
End If
Else
itemp3 = 1
End If
End If
ElseIf ((i > 24) And (i < 28)) Then
If Ctrl.Value = True Then
itemp4 = 1
End If
Else
If Ctrl.Value = True Then
itemp5 = 1
End If
End If
Next i
If ((TextBox1.Text = "") Or (TextBox2.Text = "") Or (TxtVersion.Text
= "") Or (TextBox4.Text = "") Or (TextBox5.Text = "") Or (TextBox6.Text
= "") Or (itemp1 = 0) Or (itemp2 = 0) Or (itemp3 = 0) Or (itemp4 = 0) Or
(itemp5 = 0)) Then
fValid = False
MsgBox "Please complete the form!"
Exit Sub
End If

TestValue = (TextBox1.Text)
iDateValue = (TextBox2.Text)
VersionValue = (TxtVersion.Text)
OtherValue = (TextBox3.Text)
DBVersion = (TextBox4.Text)
SerialNum = (TextBox5.Text)
SerialNam = (TextBox6.Text)

Set Book = ActiveWorkbook
Application.ScreenUpdating = False
'Open workbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data Doc.xls"
Book.Activate

' Find first available row
With Workbooks("Data Doc.xls").Sheets("Sheet1")
Counter = 1
iCellValue = .Cells(Counter, 2).Value
If iCellValue <> "" Then
Do
Counter = Counter + 1
Loop While ((.Cells(Counter, 2).Value <> "") And ((TestValue
<> .Cells(Counter, 1).Value) And (Book.Name <> .Cells(Counter,
2).Value)))
End If

' Write the values
For NameCounter = Counter To 2 Step -1
If ((TestValue <> .Cells(NameCounter - 1, 1).Value) And
((NameCounter - 1) <> 2)) Then
.Cells(Counter, 1).Value = TestValue
End If
Next NameCounter
If .Cells(Counter, 6).Value = "" Then
.Cells(Counter, 3).Value = iDateValue
End If
.Cells(Counter, 13).Value = VersionValue
.Cells(Counter, 14).Value = DBVersion
.Cells(Counter, 9).Value = SerialNum
.Cells(Counter, 10).Value = SerialNam
.Cells(Counter, 2).Value = Book.Name

For iCount = 1 To 31
Set Ctrl = Controls("OptionButton" & iCount)
If iCount < 8 Then
If Ctrl.Value = True Then
.Cells(Counter, 6).Value = Ctrl.Object.Caption
End If
ElseIf ((iCount > 7) And (iCount < 15)) Then
If ((Ctrl.Value = True) And (iCount <> 14)) Then
.Cells(Counter, 7).Value = Ctrl.Object.Caption
ElseIf ((Ctrl.Value = True) And (iCount = 14)) Then
.Cells(Counter, 7).Value = OtherValue
End If
ElseIf ((iCount > 14) And (iCount < 22)) Then
If ((Ctrl.Value = True) And (iCount <> 21)) Then
.Cells(Counter, 8).Value = Ctrl.Object.Caption
ElseIf ((Ctrl.Value = True) And (iCount = 21)) Then
If OptionButton23.Value = True Then
.Cells(Counter, 8).Value =
OptionButton23.Caption
Else
.Cells(Counter, 8).Value =
OptionButton24.Caption
End If
End If
ElseIf ((iCount > 24) And (iCount < 28)) Then
If Ctrl.Value = True Then
.Cells(Counter, 11).Value = Ctrl.Object.Caption
End If
Else
If Ctrl.Value = True Then
.Cells(Counter, 12).Value = Ctrl.Object.Caption
End If
End If
Next iCount
End With
'Close workbook
Workbooks("Data Doc.xls").Close SaveChanges:=True

Set Book = Nothing
frmLogIn.Hide
Unload frmLogIn
End Sub

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Ed]

I see two MsgBox lines in this code:

MsgBox "Please complete form prior to exiting module"
MsgBox "Please complete the form!"

I don't see one offering the user a Yes/No option. Did you leave out a
portion of code?

Ed

 

[Calligra]

Ed, Yes I did. Below is the missing portion of the code. Sorry about
that.

Private Sub UserForm_Initialize()
Dim fValid As Boolean
Dim Counter As Integer
Dim Ctrl As Control
Dim i As Integer
Dim iCount As Integer
Dim itemp1 As Integer
Dim itemp2 As Integer
Dim itemp3 As Integer
Dim itemp4 As Integer
Dim itemp5 As Integer
Dim Book As Workbook
Dim iMinCellRow As Integer
Dim iMaxCellRow As Integer
Dim iCellValue As String
Dim NameCounter As Integer
Dim ans As String


Set Book = ActiveWorkbook
Application.ScreenUpdating = False
'Open workbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data Doc.xls"
Book.Activate
Application.ScreenUpdating = True
' Find last used row
With Workbooks("Data Doc.xls").Sheets("Sheet1")
Counter = 1
iCellValue = .Cells(Counter, 2).Value
If iCellValue <> "" Then
Do
Counter = Counter + 1
Loop While .Cells(Counter, 2).Value <> ""
End If
Counter = Counter - 1
If ((.Cells(Counter, 1).Value <> Application.UserName) And
(.Cells(Counter, 2).Value <> Book.Name) And (.Cells(Counter, 1).Value <>
"")) Then
' if form is empty then
With Book.frmLogIn
.TextBox1.Text = Application.UserName
.TextBox2.Text = Now
.TextBox1.SetFocus
OptionButton23.Enabled = False
OptionButton24.Enabled = False
TextBox3.Enabled = False
End With
Else
' Read the values and place into form
For NameCounter = Counter To 2 Step -1
If ((.Cells(NameCounter, 1).Value <> "") And
((NameCounter) <> 2)) Then
TextBox1.Text = .Cells(Counter, 1).Value
End If
Next NameCounter
If TextBox1.Text = "" Then
TextBox1.Text = Application.UserName
End If

TextBox2.Text = .Cells(Counter, 3).Value
TxtVersion.Text = .Cells(Counter, 13).Value
TextBox4.Text = .Cells(Counter, 14).Value
TextBox5.Text = .Cells(Counter, 9).Value
TextBox6.Text = .Cells(Counter, 10).Value

For iCount = 1 To 31
Set Ctrl = Controls("OptionButton" & iCount)
If iCount < 8 Then
If Ctrl.Caption = CStr(.Cells(Counter, 6).Value)
Then
Ctrl.Value = True
End If
ElseIf ((iCount > 7) And (iCount < 15)) Then
If ((Ctrl.Caption = .Cells(Counter, 7).Value) And
(iCount <> 14)) Then
Ctrl.Value = True
ElseIf ((TextBox3 = .Cells(Counter, 7).Value) And
(iCount = 14)) Then
Ctrl.Value = True
TextBox3.Value = .Cells(Counter, 7).Value
End If
ElseIf ((iCount > 14) And (iCount < 22)) Then
If ((Ctrl.Caption = .Cells(Counter, 8).Value) And
(iCount <> 21)) Then
Ctrl.Value = True
ElseIf ((OptionButton23.Caption = .Cells(Counter,
8).Value) And (iCount = 21)) Then
Ctrl.Value = True
OptionButton23.Value = True
ElseIf ((OptionButton24.Caption = .Cells(Counter,
8).Value) And (iCount = 21)) Then
Ctrl.Value = True
OptionButton24.Value = True
End If
ElseIf ((iCount > 24) And (iCount < 28)) Then
If Ctrl.Caption = .Cells(Counter, 11).Value Then
Ctrl.Value = True
End If
Else
If Ctrl.Caption = .Cells(Counter, 12).Value Then
Ctrl.Value = True
End If
End If
Next iCount
End If
End With
'Close workbook
Workbooks("Data Doc.xls").Close SaveChanges:=True

frmLogIn.Show
ans = MsgBox("Is the information contained within this form still
correct?", vbYesNo)
If ans = vbYes Then
Set Book = Nothing
Call CmdOK_Click
End If
End Sub

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Ed]

Well, I'm not the greatest expert in the world, but here's my thoughts:
If I understand right, you're dealing with an error here -

frmLogIn.Show
ans = MsgBox("Is the information contained within this form still
correct?", vbYesNo)
If ans = vbYes Then
Set Book = Nothing
Call CmdOK_Click
End If

First thing I would do is set a breakpoint at

If ans = vbYes Then

then open the IDE and launch your code with F5. It will run up to the
MsgBox. When you chose Yes, it will allow you to step through from there
with F8.

Given the nature of your error
"Run-time error '91' Object variable or With block variable not set".
I'd say you've got one of three possibilities:
(a) The object variable for Book has been dropped
(b) Your Call to CmdOK_Click isn't valid
(c) There's a problem with the CmdOK_Click code.

So before you go the next step, open your Locals window and check that Book
is still set as an object variable. If not, you've got your error. If it's
okay, hit F8. See if the code steps through

Set Book = Nothing

and actually completes that. If it's good, hit F8 again.

Now you'll see if your call is good. If it's not, you'll error out because
it can't find it. If it's good, it will take you to that code. If you go
into the CmdOK_Click code, then you have a code problem, and you need to
inspect that.

I'd be willing to bet that your problem is in
fValid = False
MsgBox "Please complete the form!"
Exit Sub
End If
and the End should come before the Exit.

Try it and let us know what happens.

Ed

 

[Calligra]

Actually no. Book is dimmed as a workbook and it is still a workbook in
the watches window prior to setting it to nothing. I only added that
line of code because I had read that sometimes not setting it to nothing
can cause the Runtime 91 error. If I comment out the message box, the
code runs without error (and remove the extra dims *the whole code used
to be in the lower portion until a later version, but I was getting the
same error as I do now*).
When stepping through the project, it only throws the error when I hit
the "End Sub" of the UserForm_Initialize project. It almost acts as
though the Messagebox is only hiding and not unloaded, but I can't
figure out how to get the message box to actually close.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Ed]

Okay - I see where I goofed on the Exit and End. I copied your code to a
Word doc and highlighted every other section to make it easier to follow.

Like I said, I'm not an expert by any means, so I may not be able to help
much. Do you have two forms? Or only one? I only see a frmLogIn
referenced. At the end of the CmdOK_Click procedure, you Unload frmLogIn.
But if all of this code is in the UserForm_Initialize of frmLogIn, I can see
it causing an error if there is still more code to follow. Try moving the
Unload to the end of the Initialize. Or maybe create a Sub Goodbye with
Unload Me in it, and call that when everything is done.

Sorry I couldn't be more help.
Ed

 

[Calligra]

Ed,

Thanks for the assistance. I did find the error though and wanted to
post it for future reference. It turns out that when I initialized the
"Book" and then opened the secondary workbook, it lost it's
ActiveWorkbook setting. Although the Local and Watch windows don't show
any change with the object, I found that if I clicked on the plus sign
next to the Book in the watch window after the code stepped through
"Book.Activate", Excel would crash. If I clicked on the Book's plus
sign after the code stepped through the "With Book.frmLogin", the
properties of book had changed. I found that, being as I didn't need
the other Workbook to remain open after it got the information, if I
closed the secondary workbook prior to calling the Book's frmLogin, the
code will accept the MessageBox and exit without error. Below is the
revised frmLogin Code.

Private Sub UserForm_Initialize()
Dim Counter As Integer
Dim ctrl As Control
Dim iCount As Integer
Dim Book As Excel.Workbook
Dim NameCounter As Integer
Dim ans As String
Dim iCellValue As String


Set Book = Workbooks("Group 22--EVComm4.xls")
Application.ScreenUpdating = False
'Open workbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data Doc.xls"
Book.Activate
Application.ScreenUpdating = True
' Find last used row
With Workbooks("Data Doc.xls").Sheets("Sheet1")
Counter = 1
iCellValue = .Cells(Counter, 2).Value
If iCellValue <> "" Then
Do
Counter = Counter + 1
Loop While .Cells(Counter, 2).Value <> ""
End If
Counter = Counter - 1
If ((.Cells(Counter, 1).Value <> Application.UserName) And
(.Cells(Counter, 2).Value <> Book.Name) And (.Cells(Counter, 1).Value <>
"")) Then
' if form is empty then
Workbooks("Data Doc.xls").Close SaveChanges:=True

With frmLogIn
.TextBox1.Text = Application.UserName
.TextBox2.Text = Now
.TextBox1.SetFocus
OptionButton23.Enabled = False
OptionButton24.Enabled = False
TextBox3.Enabled = False
End With
Else
' Read the values and place into form
For NameCounter = Counter To 2 Step -1
If ((.Cells(NameCounter, 1).Value <> "") And
((NameCounter) <> 2)) Then
TextBox1.Text = .Cells(Counter, 1).Value
End If
Next NameCounter
If TextBox1.Text = "" Then
TextBox1.Text = Application.UserName
End If

TextBox2.Text = .Cells(Counter, 3).Value
TxtVersion.Text = .Cells(Counter, 13).Value
TextBox4.Text = .Cells(Counter, 14).Value
TextBox5.Text = .Cells(Counter, 9).Value
TextBox6.Text = .Cells(Counter, 10).Value

For iCount = 1 To 31
Set ctrl = Controls("OptionButton" & iCount)
If iCount < 8 Then
If ctrl.Caption = CStr(.Cells(Counter, 6).Value)
Then
ctrl.Value = True
End If
ElseIf ((iCount > 7) And (iCount < 15)) Then
If ((ctrl.Caption = .Cells(Counter, 7).Value) And
(iCount <> 14)) Then
ctrl.Value = True
ElseIf ((TextBox3 = .Cells(Counter, 7).Value) And
(iCount = 14)) Then
ctrl.Value = True
TextBox3.Value = .Cells(Counter, 7).Value
End If
ElseIf ((iCount > 14) And (iCount < 22)) Then
If ((ctrl.Caption = .Cells(Counter, 8).Value) And
(iCount <> 21)) Then
ctrl.Value = True
OptionButton23.Enabled = False
OptionButton24.Enabled = False
ElseIf ((OptionButton23.Caption = .Cells(Counter,
8).Value) And (iCount = 21)) Then
ctrl.Value = True
OptionButton23.Value = True
ElseIf ((OptionButton24.Caption = .Cells(Counter,
8).Value) And (iCount = 21)) Then
ctrl.Value = True
OptionButton24.Value = True
End If
ElseIf ((iCount > 24) And (iCount < 28)) Then
If ctrl.Caption = .Cells(Counter, 11).Value Then
ctrl.Value = True
End If
Else
If ctrl.Caption = .Cells(Counter, 12).Value Then
ctrl.Value = True
End If
End If
Next iCount
' Close workbook
Workbooks("Data Doc.xls").Close SaveChanges:=False
frmLogIn.Show
ans = MsgBox("Is the information contained within this form
still correct?", vbYesNo)
End If
End With
Set Book = Nothing
If ((ans <> "") And (ans = vbYes)) Then
Call CmdOK_Click
End If
End Sub




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!