473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Messagebox causing error in code

have a form which looks at the title of the workbook saved on a
different workbook, determines whether or not information has been
inputted previously and then asks the user if the information is still
correct. The code works beautifully until the message box is displayed.
If the user chooses, no, then the code proceeds without error. Only if
the user chooses yes is a "Run-time error '91': Object variable or With
block variable not set" message is displayed. It almost appears as
though the program is attempt to exit while the message box is still
active, but I cannot find a while to close the message box. Below is my
code, can anyone assist with 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

frmLogIn.Hide
Unload frmLogIn
End Sub
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
Call cmdOK_Click
End If
End Sub
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #1
6 1508
* Calligra <sh*************@itssiemens.com> scripsit:
have a form which looks at the title of the workbook saved on a
different workbook, determines whether or not information has been
inputted previously and then asks the user if the information is still
correct. The code works beautifully until the message box is displayed.
If the user chooses, no, then the code proceeds without error. Only if
the user chooses yes is a "Run-time error '91': Object variable or With
block variable not set" message is displayed. It almost appears as
though the program is attempt to exit while the message box is still
active, but I cannot find a while to close the message box. Below is my
code, can anyone assist with this error?


That's not VB.NET code. Post your question to an appropriate group,
like microsoft.public.excel.programming for Excel VBA questions.

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>
Nov 20 '05 #2
Hi Herfried,
That's not VB.NET code. Post your question to an appropriate group,
like microsoft.public.excel.programming for Excel VBA questions.


Friendly please, that needs nothing more?

Cor
Nov 20 '05 #3
* "Cor Ligthert" <no**********@planet.nl> scripsit:
That's not VB.NET code. Post your question to an appropriate group,
like microsoft.public.excel.programming for Excel VBA questions.


Friendly please, that needs nothing more?


I am always friendly. Sometimes implicitly.

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>
Nov 20 '05 #4
> >> That's not VB.NET code. Post your question to an appropriate group,
like microsoft.public.excel.programming for Excel VBA questions.


Friendly please, that needs nothing more?


I am always friendly. Sometimes implicitly.


I know and therefore it looks so nice when you do it explicitly because
others do not know that.

:-)

Cor
Nov 20 '05 #5
* "Cor Ligthert" <no**********@planet.nl> scripsit:
That's not VB.NET code. Post your question to an appropriate group,
like microsoft.public.excel.programming for Excel VBA questions.

Friendly please, that needs nothing more?


I am always friendly. Sometimes implicitly.


I know and therefore it looks so nice when you do it explicitly because
others do not know that.

:-)


:-)

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>
Nov 20 '05 #6
Apologize Gentlemen for posting inappropriately. I appreciate your
insite, however, it still throws the same error even when even the code
is re-written to the appropriate sub. I have reposted it to the Excel
programming site. If anyone can assist there, I would really
appreciate.

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Drew Lettington | last post by:
I'm making a simple call to display error messages in a MessageBox from a Windows form and the MessageBox is not behaving in a modal fashion. My modal form displays, the user clicks a button and...
4
by: Tressa | last post by:
I have a messagebox that I only want to pop up only if it is not already being displayed on the screen. My code is still poping up the messagebox even though it is on the screen. What am I doing...
3
by: Sin | last post by:
I'm currently evaluating VC.NET as the new platform for the company I work for and things are looking grim... We're up against another IDE which took me about 5 minutes to master and I've been...
5
by: Robert Heuvel | last post by:
Hi, this is what I did: public struct SWaitCursor:IDisposable { public SWaitCursor (int i) { Cursor.Current = Cursors.WaitCursor; } void System.IDisposable.Dispose() { Cursor.Current =...
20
by: Peter E. Granger | last post by:
I'm having a strange problem (or at least it seems strange to me) trying to display a MessageBox in a VC++ .NET forms application. If I put the call to MessageBox::Show in the form's .h file, it...
12
by: babak | last post by:
Hi everyone I want to format a string (using sprintf) and put it in a messagebox but however I try to do it, it doesn't seem to work. Here is an example sample of what i try to do: char msg;...
10
by: Russ | last post by:
I've been trying to figure out how to show a simple messagebox with an OK button in my web client program (C#). I have looked at every reference to JScript and MessageBox that seemed even remotely...
5
by: Muffin | last post by:
I hope somebody can show me why I need to have a messagebox to get "random" numbers in my example. If I comment out the message box call that is in RollAbility() the numbers generated are not...
9
by: sovht | last post by:
System: Intel, Windows XP Pro, SP2 IDE: VC++ 6.0 Problem: *Very* simple program to create a MessageBox only ever displays the first character of the given string. I checked the spec for the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.