Hi Team:

I am trying to get the hang of these forms and their ability to gather a lot of information from the user quickly, but I have not spent much time with them on the Excel side and I fear I'm breaking rules or approaching from the wrong angle. I have a pretty complex form that I'm trying to debug as i go. So the only have a few Private Subs to start with. Below is everything I have.

Problem:
1) when I Show the form and click on the ProdYear RefEdit box I want the current selection to change to Range("a1"). That works nicely. But once a cell is selected and the RefEdit box closed or moved off of; I want the caption of a lable to be changed to indicate the number of rows in the dataset to be processed. When I click on another item in the form one of two things happens. Either the form closes (just goes away) or it "sticks" and I can't close it with the cancel button (click event for cancel is 'end')

Questions:
1) Is 'end' a good thing to put in the Cancel button click to just shut things down?
2) am I using the wrong event for the RefEdit box to make the lable.caption change
3) am I doing something wrong in the way I handle the code in the RefEdit box.

____________________________________

Option Explicit
Public OverRun As Boolean
Private Sub CBProdCancel_Click()
End
End Sub


Private Sub CBProdExicute_Click()
MsgBox "leaving now"
End
End Sub

Private Sub ProdYear_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rowcnt As Long
rowcnt = ActiveSheet.Range(Me.ProdYear.Value, Range(Me.ProdYear.Value).End(xlDown)).count
If rowcnt < 3461 Then
Me.ProdInfo.Caption = "There are " & rowcnt & " rows of annual data. Output will be placed " & _
"on a single worksheet named Combined."
OverRun = False
Else
Me.ProdInfo.Caption = "There are " & rowcnt & " rows of annual data. Output will be placed " & _
"on a two worksheets named Combined1 and Combined2."
OverRun = True
End If
Cancel = True
Me.ProdInfo.Visible = True
End Sub



Private Sub ProdYear_DropButtonClick()
ActiveSheet.Range("a1").Select
End Sub
_________________________________

I've attached a screen shot of the form. Is there a bette way to show a form to the forum then this?

Thanks for any tips you could provide

I have had better luck with the AfterUpdate event of a combo box.

Also, you will want to spell Execute correctly.

Frank,

I tried AfterUpdate to start with and am having the same result. Thanks for the spell check!

The sucker locks up and crashed Excel now. I'm running this form from my Personal.xls; is that a problem?

What about this line, Paul?

rowcnt = ActiveSheet.Range(Me.ProdYear.Value, Range(Me.ProdYear.Value).End(xlDown)).count

shouldn't it be this?

rowcnt = ActiveSheet.Range(Me.ProdYear.Value, Range(Me.ProdYear.Value).End(xlDown)). Rows. count

Frank,

Yes Sir, it very well should. Thank you

I made the change and the form still hangs. Cancel button, X, Esc...all do nothing. the form stays active and live, but will not close or cancel. I can move to the editor and hit the reset button and it crashes Excel.

Challanges Challanges

Paul, yes, it never stops. I must confess to being out of ideas at this point. Could it be a corrupt file or form?

Can you copy the data to a new workbook and export the form from the malfing wb to the new one?

The problem lies in the RefEditbox.Value it returns a string containing the sheet name as well as the range. Makes sense really but now I need to refer to the range that looks like Sheet1!$A$1.

Do I need to strip the cell ref off this before using? Or is there another way to make the reference with the sheet name attached?

I'm a blank.

I would try that. You can at least strip the sheet name and cell reference into their own variables and use just the cell reference. Then if you later need the sheet name you would have that too.

I will give it a whirl.

I have removed the problem code and assigned RowCnt = Me.ProdYear.Value
The dang thing still will not respond to the Cancel button (I assigned cancel=true to this one) or the Execute button (properly spelled) that has 'end' as the only code line in the _Click event.

Esc doesn't kill it either, but I can X out currently.

What is up with this?

Paul, the code is just not complex enough to be causing all these problems, is it? You don't have any other code running while this is running do you? No hidden Excel apps doing dastardly things to RAM and such as that?

Have you tried exporting the form to a brand new workbook? Or putting a new refedit control on the form?

Edited by: fkegley on Fri Nov 18 14:35:26 EST 2005.

Frank,

I give export a try after rebuildign the refedit control. But your right, there's nothing there to go wrong. As for hidden stuff running...excel has crashed 5 time and I rebooted for the heck of it...so, I'd say NO.

Thanks for the encouragement, I'll check back when I have the changes made.

Thanks, Paul. I would like to know what's going on.