VB6 Stop that command click

[Dave]

I've been trying to ensure that a VB6 form's command button is only clicked once. Clicking twice gets the "component request pending" error message (action can't be completed because other application is busy etc... switch to/retry). I've tried enabling (T/F) the button (same result), and the code below, which does change the command button to red but still generates the error if the button is clicked again while the form is visible. Hiding the form before calling the InsertFormula routine doesn't work ie. quickly double clicking the button causes the same error. The insert formula routine open's an XL file, inserts formulas then saves the file and quits the application. Any suggestions will be appreciated. Dave
[VBA]
Private Sub command2_Click()
If Form1.Command2.BackColor = RGB(255, 0, 0) Then
Exit Sub
End If
Form1.Command2.BackColor = RGB(255, 0, 0) 'red
Call InsertFormula
Form1.Hide
[/VBA]

[lucas]

You can disable the button until controls are filled in excel anyway...not sure about vb:
[VBA]Private Sub Userform_Initialize()
ComboBox1.AddItem ("12")
ComboBox1.AddItem ("Joe")
ComboBox1.AddItem ("Joe")
ComboBox2.AddItem ("is")
ComboBox3.AddItem ("cool.")

CommandButton1.Enabled = False
End Sub[/VBA]

then enable it if conditions are correct:
[VBA]Private Sub ComboBox1_Change()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub

Private Sub ComboBox2_Change()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub

Private Sub ComboBox3_Change()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub[/VBA]

[Dave]

Thanks Lucas but changing the enabling does not work. Neither does using cancel. Hmm? Dave

[Bob Phillips]

Dave

Can you post the VB6 project to save us re-inventing it?

And the Excel book if there is one.

[XLGibbs]

WHat is Insertformula doing? Is the problem within that?

[Dave]

Thank you both for your interest and anticipated assistance. I've made some test files which represent the problem. The XLS file goes directly to the "C" drive. I'll post again with the VB6 file. You will probably need to use the task manager to stop the XL application if you click the VB6 form button too fast. Dave
The insertformula routine is as follows...
[vba]
Sub InsertFormula()
Dim objExcel As Object, objWorkBook As Object, objWorksheet As Object

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = "=B1+C1"
objWorkBook.Close SaveChanges:=True
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(4)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub
[/vba]

[Dave]

Here's the VB6 test project. Dave
ps. just noticed that if you click the VB6 form's button really fast, it actually clicks itself and repeats the button's routine after the XL form unload.

[XLGibbs]

VB project is missing the other components, like the form and the code.

Have you tried debugging without the error handler to see what error it throws? line by line using F8?

[XLGibbs]

[VBA]Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True
End Function [/VBA]
May want to have that quick IsWbOpen check (courtesy of firefytr's KB entry) before the process runs. Seems to be if the workbook is open, and or currently saving when the button is clicked that it would cause some issues.

I would check if it is already open and if so, exist sub.

[Dave]

That didn't seem to work. Apologies re. VB6 file. I'll try again. Dave
edit: that didn't work either. It says it's the vbp file? I'll try it again

[Dave]

Made it .exe. Dave
edit: still doesn't give code. Here's the VB6 form code. The project just has a form with a command button1 on it. Dave
[VBA]
Private Sub command1_Click()
Dim objExcel As Object, objWorkBook As Object
Dim Clickcnt As Integer, objWorksheet As Object
Clickcnt = Clickcnt + 1
If Form1.Command1.BackColor = RGB(255, 0, 0) Then
Exit Sub
End If 'red
Form1.Command1.BackColor = RGB(255, 0, 0) 'red
Call InsertFormula
Form1.Hide

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
objExcel.Run "showuserform1"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Form1.Show
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(3)"
MsgBox "You clicked: " & Clickcnt & " times!"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Form1.Show
End Sub

Sub InsertFormula()
Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = "=B1+C1"
objWorkBook.Close SaveChanges:=True
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(4)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub

Sub RemoveFormula()
Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = ""
objWorkBook.Close SaveChanges:=True
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(5)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub

[/VBA]

[Tommy]

Hi Dave,

Why not as lucas has pointed out use the enabled feature?
[vba]
Private Sub command1_Click()
Dim objExcel As Object, objWorkBook As Object
Dim Clickcnt As Integer, objWorksheet As Object
Command1.Enabled = False '-Added
Clickcnt = Clickcnt + 1
If Form1.Command1.BackColor = RGB(255, 0, 0) Then
Exit Sub
End If 'red
Form1.Command1.BackColor = RGB(255, 0, 0) 'red
Call InsertFormula
Form1.Hide

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
objExcel.Run "showuserform1"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Command1.Enabled = True '-Added
Form1.Show
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(3)"
MsgBox "You clicked: " & Clickcnt & " times!"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Command1.Enabled = True '-Added
Form1.Show
End Sub

Sub InsertFormula()
Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = "=B1+C1"
objWorkBook.Close SaveChanges:=True
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(4)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub

Sub RemoveFormula()
Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = ""
objWorkBook.Close SaveChanges:=True
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "XLap error(5)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub



[/vba]

[lucas]

Hi Tommy,
This is a little out of my league...
I may have to install vb6 again.....

When We going fishin?

[Dave]

Thanks Tommy for your input. That does seem like a simple solution that should work... but it doesn't. In the "real" project the insert formula section contains 9 lengthy formulas to insert which I assume takes a few seconds to accomplish (along with the opening of the XL file, the formula insertion, and then saving the file). I thought that perhaps the formula insertion was generating a calculation event so I tried setting it to manual before formula insertion then returning to automatic...didn't work. I trialled my test XL file posted with the .exe file posted and couldn't seem to replicate my difficulties even though running the program from VB6 does create the same difficulties as previously stated. I'm going to trial combining the insert formula routine into the command button routine. Dave

[Tommy]

So it is not about the double-click of the button? I got the same problems but I was short sighted and narrow minded because I was looking at the button click. I'll look at it again tonight, I'm under the gun all the time at work.

Hey Steve,

I was up in Dallas last week training and installing new hardware and software. Started to give you a shout but things (as usual) got stretched out longer than I wanted so I had to come back late.

[Dave]

Tommy your vision is 20/20... it is about the double click. Apologies for the confusion. Just trialling different ways to thwart this annoying problem. Combining the insert formula routine into the command button click code seems worse (ie. you must use the task manager to stop the XL application before the same error will clear). This seems like more efficient code but the double click result is worse? Dave
[VBA]
Private Sub command1_Click()
Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object

Form1.Hide

On Error GoTo ErFix
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\clicktest.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(1, 1).Formula = "=B1+C1"
objExcel.Run "showuserform1"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Form1.Show
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "XLap error(3)"
objExcel.DisplayAlerts = False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
Call RemoveFormula
Form1.Command1.BackColor = RGB(0, 128, 64) 'green
Form1.Show
End Sub
[/VBA]

[Tommy]

The problem is in the
[vba]objWorkBook.Close SaveChanges:=True[/vba] doesn't work, at least the way I thought it would so ....

[vba]
objWorkBook.Save
objWorkBook.Close
[/vba]
LOL I tested with the code I posted earlier I also made some functions to open excel and close excel so it would be easier to debug. The project is attached.

You could import the userform to vb6 if you wanted.

EDIT: Added attachment

[Dave]

Tommy you need to do this for full effect...
[VBA]
'objExcel.Visible = True
[/VBA]
My testing... quickly clicking the form button more than once hides the VB6 form then shows the XL app userform (until closed by selecting the quit button), but then continues on showing the VB6 again and then the XL userform...without clicking the VB6 botton again (auto like). This continues on for the number of clicks...trialled 2-4 clicks....if you really get carried away clicking fast you can still generate that error. The code is much improved to my previous attempts (doesn't crash nearly as easy). Thanks for your continuing time and efforts. I'm going to trial a few more things. Dave

[Tommy]

The reason for the visible = True is to make sure the code was workong and I didn't have to do the 3 finger salute to get rid of all the instances.

I can't click it that fast. I would suggest at this time to make a global variable in the form level and not execute the macro if it is less than a second.

I pick the button as fast as I can but I couldn't catch but 1 at a time. It does process the InsertFormula, RemoveFormula, and show a user form, each one closing and saving the test spreadsheet from 1 click.

[Dave]

Thanks again Tommy for your help. I should have mentioned that "5" in the XL userform textbox indicated formula insertion success. Strange that my continued test findings of quickly clicking the VB6 form button while it is visible, produces the show XL userform.. on "quit"(XL userform command button) shows the VB6 form and auto clicks the command button to again hide the VB6 form and then show the XL userform. This cycles for each initial VB6 command click. The VB6 form command button looks disabled during this cycle. My pc glitch apparently. I'm not sure if I follow your suggestion re. global variables and not executing the macro if it's less than a second? If I come up with something that works for my pc, I will post. Dave