Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
Hi,
We've been using this since August 2007. This just started happening about two weeks ago: In Excel 2000, the application freezes when the user types in the combobox to select an entry. The worksheet starts calculating and won't stop. If the user scrolls to the selection and presses enter the application does not freeze. I'm using the following code in the KeyDown event: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select End Sub Also, I'm populating the combobox from a named range--PARTNUM, that is growing daily. It has 1396 rows of data and we add about 27 items per week. I can stop this locking up behavior if I select manual calculation, but then the user has to remember to calculate manually. It seems I should turn off automatic calculation when the user makes a selection in the combobox and then turn it back on when they move on to the next cell. Has anyone experienced this before? I tried adding the calculate command to my code but it fails with the message, Compile Error, Expected Function or variable. Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Fails here-- Application.Calculate = False Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculate = True End Sub I appreciate your suggestions. Thanks, Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
Hi,
I also tried: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Application.Calculation = xlCalculationManual Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculation = xlCalculationAutomatic End Sub This didn't return an error message, but the application froze and ctrl +alt+F9 did not stop the calculation. Thanks for any feedback. Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
Application.Calculate = False
Should be: Application.Calculation = xlCalculationManual Application.Calculate = True Should be: Application.Calculation = xlCalculationAutomatic "dan dungan" wrote: Hi, We've been using this since August 2007. This just started happening about two weeks ago: In Excel 2000, the application freezes when the user types in the combobox to select an entry. The worksheet starts calculating and won't stop. If the user scrolls to the selection and presses enter the application does not freeze. I'm using the following code in the KeyDown event: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select End Sub Also, I'm populating the combobox from a named range--PARTNUM, that is growing daily. It has 1396 rows of data and we add about 27 items per week. I can stop this locking up behavior if I select manual calculation, but then the user has to remember to calculate manually. It seems I should turn off automatic calculation when the user makes a selection in the combobox and then turn it back on when they move on to the next cell. Has anyone experienced this before? I tried adding the calculate command to my code but it fails with the message, Compile Error, Expected Function or variable. Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Fails here-- Application.Calculate = False Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculate = True End Sub I appreciate your suggestions. Thanks, Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
I should have read part two.
"dan dungan" wrote: Hi, I also tried: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Application.Calculation = xlCalculationManual Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculation = xlCalculationAutomatic End Sub This didn't return an error message, but the application froze and ctrl +alt+F9 did not stop the calculation. Thanks for any feedback. Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
Well, at least I seem to be on the right track! Thanks for your
response. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
This sytax from the help files, does not look like the syntax you are using,
and it gives no explanation for using your syntax. Maybe this is your problem. Private Sub object_KeyDown( ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As fmShiftState) "dan dungan" wrote: Hi, I also tried: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Application.Calculation = xlCalculationManual Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculation = xlCalculationAutomatic End Sub This didn't return an error message, but the application froze and ctrl +alt+F9 did not stop the calculation. Thanks for any feedback. Dan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
Here is another example from the help files:
KeyDown Event, TextBox Control, CurLine, CurTargetX, CurX, Text Properties Example Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) TextBox2.Text = TextBox1.CurLine TextBox3.Text = TextBox1.CurX TextBox4.Text = TextBox1.CurTargetX End Sub I used this syntax to allow moving the cursor if the user pressed tab or enter because it seems there is no other way for the user to get out of the combobox except by using the mouse. Dan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
As usual I didn't read far enough. I have never used this method before, so
I will have to play around with it to get to understand how it works. But offhand, I don't see what would cause a calc loop. I know it sometimes happens in BeforeClose events if the calcultion isn't autocalc isn't turned off. Interesting. "dan dungan" wrote: Here is another example from the help files: KeyDown Event, TextBox Control, CurLine, CurTargetX, CurX, Text Properties Example Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) TextBox2.Text = TextBox1.CurLine TextBox3.Text = TextBox1.CurX TextBox4.Text = TextBox1.CurTargetX End Sub I used this syntax to allow moving the cursor if the user pressed tab or enter because it seems there is no other way for the user to get out of the combobox except by using the mouse. Dan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox-calculating issue
I went to tools/options and selected manual calculation.
Then I change the code to: Private Sub cboPartnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab Worksheets("QuotedPart").Range("d2").Activate Case 13 'Enter Worksheets("QuotedPart").Range("d2").Activate Case Else 'do nothing End Select Application.Calculate End Sub This solves the problem of the "calculation loop" that I can't determine the cause for. However, it bring up these questions for me: This file is an .xlt file placed on the network 1. If the use has other workbooks set to automatic calculation, how will this effect my template? I'm going to use xlCalculationManual on work_book open. 2. The user is forced to press F9--possibly several times--to complete the quote. There are cells to populate with values depending on the part number to include all the charges. I'm going to use application.calculate on worksheet_selection change. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox Issue | Excel Programming | |||
Combobox issue! | Excel Programming | |||
Combobox Issue | Excel Programming | |||
Another ComboBox Issue | Excel Programming | |||
Another ComboBox Issue | Excel Programming |