Excel VBA userform.show does not display form locks excel

[hapnendad]

I am using a selection_change event on a worksheet to launch a userform that will populate the cell when a selection is made from a drop down list box on the form. Selection_change shows form if cell to the left of the active cell meets a certain condition and the active cell is in column 8.

Problem: when conditions warrant, selection_change shows userform. when executed Excel locks. VBA editor indicates code is running but form can not be seen.

Some Details: This is a very large spreadsheet with lots of formating in the cells, borders, etc. Sheets are protected to select unlocked cells, edit objects, edit senarios, insert hyperlinks. worksheets contain several command buttons and a calendar 11.0 control. Worksheet only navigable using controls and forms, Hidden items: AllToolbars, some right click menu items, sheet tabs, horizontal scroll bar. All sheets set to same zoom level(80%). forms are launched so as not to be atop other controls. all sheets reached by controls contain activate, change, and selection_change events.

Contents:
worksheets: 34
named ranges:45
userforms:24 (all modal)
modules:11

Using Excel2003 on XP pro >2gig Ram

Problematic code:
If CCol = 8 And UCase(Cells(CRow, "E").Value) = "X" Then
If ActiveCell.Value <> "" Then
resp = MsgBox("Do you want to replace " & ActiveCell.Value & " as the responsible party" & vbCr & "to complete Task" & ActiveCell.Row & "?", vbYesNo)
If resp = vbYes Then GoTo showit
If resp = vbNo Then GoTo dont
End If
showit:
With UserForm3.Label1
.Caption = "Assign a resource to complete task " & ActiveCell.Row & "."
End With
With UserForm3
.Left = ActiveCell.Left + 35
.Top = ActiveCell.Top
.Show '<-----code and excel locks on this instruction
End With
dont:
changeflag = False

Any ideas please? This has driven me nuts. The real kicker is that the code works on a different sheet. I have written the code to be able to copy from sheet to sheet without need of change.

[kadghar]

Im not sure if it should cause any problem, but have you checked that the startupposition of the form is set to manual?? if not, just put something like:

With UserForm3
.StartUpPosition = 0
.Left = ActiveCell.Left + 35
.Top = ActiveCell.Top
.Show '<-----code and excel locks on this instruction
End With

Anyway, i dont think that should have been a problem... if it wasn't set that way the form should only have been displayed somewhere else.
(I dont see any other problem with that very part of your code)

Hope that helps.

[hapnendad]

kadghar,

I did have userform3 set to manual. I may go back and try your fix but found it all works fine when set to center screen. I was trying to move the form to be near the cell it was to pupulate. what appears to have been hapening is that the form loaded but seemed to be tied to the previous cell as the userform launched from a selection change event. I I realized after I posted that the activate code that caused the change event may have been many rows away from the target cell (the page it worked fine on uses only 40 rows, those it did not work on had several hundred. On a whim, when the code seemed to lock, I used the down arrow and return and the cell was populated without problem. I decided to leave out the move it to the cell gimic.

Many thanks for the quick response.

J