473,231 Members | 1,815 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Excel VBA userform.show does not display form locks excel

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.
Jun 27 '07 #1
2 12479
kadghar
1,295 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. With UserForm3
  2. .StartUpPosition = 0
  3. .Left = ActiveCell.Left + 35
  4. .Top = ActiveCell.Top
  5. .Show '<-----code and excel locks on this instruction
  6. 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.
Jun 27 '07 #2
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
Jun 28 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Matthew Wieder | last post by:
I have the following requirements: Build a stand-alone C# application that asks the user to click in a cell in an Excel spreadsheet, and then displays the address of that cell in the C#...
1
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access...
3
by: gw.boswell | last post by:
I have created a userform with textboxes for data entry. I also have a command button that calls a second useform. A user enters certain information in the first useform but if he/she wants to do...
1
by: elLiven | last post by:
Hi I'm trying to learn VBA in Excel mysel. Getting stuck on userforms. I can create a userform with optionbuttons and command buttons. I want to know how to have the form display the...
7
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
1
by: brenty66 | last post by:
I have a userform that is used as an interface for information stored on excel spreadsheets. The user enters bits of information into fields on the userform which are place on the corresponding...
3
by: brenty66 | last post by:
I have a userform used as an interface to store information on excel spreadsheets. I have put a decent amount of work into it to this point and all of a sudden I am getting a catastrophic error...
1
by: Pippy Parker | last post by:
I've created a Userform within an Excel session which is populated by various pieces of info. What I'm looking to do is exported the Userform into a jpeg which I then can attach to a e - mail. ...
3
by: mforema | last post by:
Hello, I have a userform in Excel with a textbox and cmd button. I want to give the user the ability to search for a specific worksheet name within an Excel Workbook. My code for the cmd button...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.