Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message box causing error in code
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's causing error message? | Excel Worksheet Functions | |||
cells without values causing error message | Excel Discussion (Misc queries) | |||
Formula Causing a Save Error Message | Excel Worksheet Functions | |||
Message box causing error in code | Excel Programming | |||
code that might be causing error - please review and comment | Excel Programming |