Focus Activecell After Display Of Modeless Userform

May 7, 2009

I am using a technique to display a modeless userform to display to the user that the cell data validation has failed (or not) after checking cell data entry (with checks coded as required).

I am using this method for a number of reasons -
- enables more flexibility and more characters over that of in cell data validation error messages
- to reduce repeat of validation data (to reduce file size) as worksheet has large qty of rows with large qty of columns with variation of validation requirements for each column.

But in the event that data is invalid (fails validation), once the modeless userform is displayed, i want the focus to be on the current cell, not the userform, otherwise the user will always have to click the worksheet to to get focus to enable entry to cell of corrected data.

View 5 Replies


Modeless Userform :: Set The Focus Back On The Range

Aug 1, 2008

I call a userform (modeless), and then i'm trying to set the focus back on the range that was selected when i showed the userform. you would think i could just define the range and then use like this....

set prevrnge = selection (vbmodeless)

but it doesn't take the focus off the userform....

View 9 Replies View Related

Force Focus To Range From Modeless UserForm

Nov 21, 2007

A userform to move the curser to a column then activate the Excel App and keep the userform visiable.

The problem is when excution the code step by step (F8) works fine, but when excution the code by F5 the cursor moves to the Range("A65536").End(xlUp) and doesnot then move to the required column.

also this line
AppActivate ("Microsoft excel")

does not work , it gives me Invalid proceduers or call argument.

Private Sub ComboBox1_Change()

If ComboBox1.ListIndex = 0 Then Cells(ActiveCell.Row, "I").Activate
If ComboBox1.ListIndex = 1 Then Cells(ActiveCell.Row, "J").Activate
If ComboBox1.ListIndex = 2 Then Cells(ActiveCell.Row, "K").Activate

AppActivate ("Microsoft excel")

End Sub

View 9 Replies View Related

Using Modeless Userforms And Losing Focus

Jun 8, 2006

I have an application where by the user only sees userforms which then writes the data to the back end worksheets. So the Menu or front useform I have set at vbModeless, which also means that the user can now open up or use an already opened spreadsheet. I believe I am correct in saying you can't do this if the userform is modal. Anyway this is what the user wants, i.e to be able work on multiple spreadsheets, whilst my app is open. The problem is when they click on one of the other open spreadsheets and then click one of the buttons on my application, you get an error.

I believe is because I use code such as


Where I haven't specified the actual workbook, so excel doesn't know where to go to find the userform. So how do I specify the workbook, without constantly having to change the line, when version numbers changes ?


For the next revision the file may be called Book1.1

View 2 Replies View Related

Userform Not Rendering When Shown Modeless

Mar 26, 2014

I have a sub that is called when the user clicks a button on the form.

The sub takes a few seconds to run, so I have created a userform which contains a single label, which simply says "working ... please wait" in large letters.

A code example is as follows:

[Code] ....

Seems simple enough, but what actually happens is that the PleaseWait userform is displayed, but contains nothing (i.e. blank white - the contents is not being rendered). I'm guessing that I need to call something on the userform to force it to render its contents after the modeless call to show it. How can I do that?

View 1 Replies View Related

Change Caption In Modeless UserForm From Another

Feb 2, 2008

Background: I currently have an some VBA code that will kick off a series of calculations on a remote server (7 calcs). I am showing a modeless form, to allow the user to continue to work in Excel while the external calculation is taking place. The code checks every few seconds (using .OnTime) to check the status of the calcs, and as one finishes, it starts the next calc, and updates the form (ie - "Running Calc #3 of 7", etc).

'Do calc
'Finish calc
frmProgress.labMessage.Caption = "Running Calculation: 'Revenue'"
'Do another calc
'Finish another calc
frmProgress.labMessage.Caption = "Running Calculation: 'Labor'"

Problem: when I change the label on the form, it gives the form focus. So, if the user is in the middle of doing something, they are suddenly stopped, and have to click back on the spreadsheet to continue.

Questions: Is there a way to update content (label) on a form, without that form getting focus? If not, is there another way to provide updated feedback without disrupting what the user is doing? My fallback is to simply not update the form with progress reports, but since the calcs can take 5min or so, it would be good if they knew things were progressing.

View 3 Replies View Related

Modeless UserForm :: Loop Showing Progress Bar

Jul 10, 2009

On opening of my WorkBook I refresh my PivotTable and that takes a while so while its refreshing. So I want to launch a UserForm that basically have a loop showing progress bar similar than the Windows loading bar (i.e. a set of green vertical green bars moving in a black rectangle).

Here is the code in THisWorkbook:

View 9 Replies View Related

Userform Text Based On ActiveCell

Jul 20, 2009

As usual, I think this might be a really simple one but I canít seem to find the answer. I want to create a userform that has text at the top (not the title bar) that looks at the value in the ActiveCell.

e.g. "Please enter information for " & ActiveCell

I have tried to place this in a text box and the code for the text box but nothing seems to work I am pretty new to userforms so could well be going about this completely the wrong way.

View 2 Replies View Related

How Can I Change A Modal Userform From Modal To Modeless At Run Time

Aug 9, 2009

I am wondering if one can force a Modal UserForm to become Modeless after loading the UserForm.

Load the Userform as Modal => Click on a CommandButton on the Userform => The Userform now becomes Modeless.

View 9 Replies View Related

Setting Focus On Userform

Jul 7, 2009

I have a userform that is called by clicking on a button and it copies the value in cell A to my userform. This works great it is called via:

View 5 Replies View Related

Set Focus To TextBox On UserForm

Oct 16, 2006

On the summary page click on 'Enter Sale' and notice that Customer Advisor' text box gets the focus and the cursor is flashing. (Which is what I want). Enter any name and a sale value (say 'A' and '1') and click the 'OK' button
But when I tried to enter another sale the OK button still had the focus. So in the VBA Project / Forms / frm_sale / OK Click event - near the bottom
I added a set focus before the 'frm_sale.hide' - This removed the focus from the 'OK' box - but the cursor is still not in the text box, and it needs to be clicked in order to enter text. This is clearly something trivial I am missing - but the blinkers are on and I cant spot it

View 5 Replies View Related

Set Focus To A Textbox When Userform Opens

Jul 29, 2009

I'm creating a userform for editing membership records of a small organisation. So I want the focus to default to textbox tboxFind which is used for searching the data.

I've put tboxFind first in the tab order, and in subroutine Userform_Initialize, the last line is tboxFind.SetFocus

Using Excel2000 on Windows Vista, it works OK.

Opening the same workbook in Excel2000 on WinXP, no textbox has the focus when the form opens, but once the user has started to use the form, the focus defaults back to tboxFind, as it should.

So why is the focus not where it should be when the form opens in WinXP?

View 11 Replies View Related

To Set Focus To The RefEdit Control In Userform

Feb 23, 2007

I am trying to implement a simple userform using the RefEdit control.

So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton.

For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control?

Right now I have the Ok button Default property set to True.

I have a commandbutton on the spreadsheet that activates the userform.

View 9 Replies View Related

Change Sheet Focus From UserForm

Jun 6, 2006

I've got a userform for entering data in a workbook with sheets for each month. ie: Jan'06 , Feb'06, etc. I've added a combobox that looks at my list of these sheets and when a certain month is chosen I would like that months sheet to come up behind the userform, so that the data is entered there when the form is completed.

The other option is to not change the sheet focus but to direct the data to the appropriate sheet based on the combo box. (No matter what sheet is currently selected)

On a related note, I have a text box showing the last date entered and I would like to make shure that it is updated based on the sheet selected in the combobox.

View 9 Replies View Related

Set Focus To Application After Hiding UserForm

Sep 21, 2007

I created UserForm with one TextBox and "Close" Button. And it's works like a real-time search with my criteria. He is using AutoFilter to show what he found. And when I'm pressing Close Button the Focus isn't on Application, but on Hidden window (know because when I'm clicking alt+space form's menu shows).

To show window I'm using (running from hot-key):

DoCmd.OpenForm "UserForm1"

to hide

Private Sub CloseButton_Click()
End Sub

View 9 Replies View Related

Set Focus To TextBox On UserForm Initialize

Mar 7, 2008

I am trying to SetFocus on a single textbox in a simple form. I am not sure if I am doing it right.

Private Sub UserForm_Initialize()

' On Error Resume Next

' Dim rheadings, cl As Range
' Set rheadings = Worksheets("CONTACT").Range("A1:F1")
' For Each cl In rheadings
' Me.cbxSearchWhere.AddItem cl.Value
' Next cl

With Me
End With
End Sub

Kind regards, Mentor Auto Merged Post Until 24 Hrs Passes;Hi: I answered my own query - just commented out the setfocus instruction and it defaults to the first, and only, textbox by default

View 2 Replies View Related

Keep UserForm Linked To Particular Workbook Even If Focus Is Lost

Jan 19, 2010

I have an excel program that uses a modeless userform. often times i will open other spreadsheets while the form is active or minimized. if i return to the form and run it without manually bringing focus to the appropriate workbook, i get an error because it references the wrong workbook. is there a way to keep that form tied to that workbook no matter what? also, it is important that this procedure works despite the workbook name changing.

View 5 Replies View Related

Return Focus To Sheet While Still Displaying A UserForm

Dec 16, 2009

I have a spread sheet where i want to display help text when cells are activated. I do not want to use comments as i both do not like the red triangle and also have not found a way to controll the position of the text box. When I use the event Worksheet_SelectionChange I can display a custom box but the box "retains focus" and i can not enter the data until i "reclick" on the cell in the sheet. The box then goes away when i select another cell. Is there a way to fix this or a different method entirely. I am using Excel 2007.

View 9 Replies View Related

Set Focus Of Cursor In TextBox On UserForm Show

May 18, 2007

I'm having some trouble setting up my Userform. It's used for someone to insert a password to open up some sheets. I have it working except for 2 problems.

First off it gets started by a sub which has a shortcut key, CTRL + W.

When it opens I would like the TextBox to be ready to be typed on... and I would like the submit button ready for Enter. So when it opens, you just type in the password really quick and press enter without using the mouse at all.

The TextBox starts on the first try, but when I press cancel and then open the UserForm up again, then the cancel command is still selected and I have to select on the textbox.

There is 3 things on this userform... TextBox1, CommandButton1, CommandButton2. All I want is for the TextBox always to show up ready for typing, and the Submit(CommandButton1) to always be ready to press enter.

View 4 Replies View Related

Return Focus To Worksheet From Userform 2007 Vs 2000

Jun 27, 2009

I have a workbook developed in Excel 2007 (compatibility mode) that contains Userform4. This form can be displayed (or not) depending in the wishes of the user. What I am trying to do is return focus to the worksheet after the UserForm is displayed.

When the worksheet is activated, I have the following .........

View 14 Replies View Related

Range(activecell, Activecell.end(xlright)).copy

Apr 6, 2007

Does this code copy all cells from the active cell up to the last non-blank cell, or is it up to the first blank cell after the last non-blank cell?

View 9 Replies View Related

Minimize Userform And Give Focus To Immediate Next Window When Button Clicked

May 10, 2014

I currently have a small userform placed at the bottom right of the screen (with a command button on it) and this opens up on top of excel instances and other applications/windows and it is set so that you can't interact with anything except the userform.

I wanted to minimize the userform and give focus to immediate next window (can be excel or non excel applications) for 10 seconds and then activate/show the userform back again, when the "Button 1" on the userform is clicked.

I tried hiding the userform, but this doesn't give focus to the immediate next window. Below is the code

Private Sub Button_Click()
PauseApp (10)
End Sub

I could only find answers for adding minimize and maximize buttons to userform.

When the button is clicked, it should perform

Step a - Minimize the userform
Step b - Give focus to the next immediate window behind the userform
Step c - Wait for 10 seconds
Step d - Bring back the userform its to state.

View 2 Replies View Related

Force Focus Back: Pop Up A Userform To Provide Instructions And Collect Some Additional Data From The User

Feb 18, 2009

I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.

The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?

View 2 Replies View Related

Text Missing In Modeless Form

Aug 15, 2006

In Excel 2003, I built a small form with only a text box in the form that says "Thinking...Please wait! When I call it from code using:

' my code has some input boxes before this vbModeless

' long running code after this

End Sub

the form displays, and my code continues, but the text box on the form is not displayed.

View 2 Replies View Related

How To Display Lat Row Value In UserForm

Dec 21, 2012

In a sheet I am showing a calendar where a user can choose Day, Month, Year and if he/she presses enter the data is submitted in Sheet 3. This is working fine for me.

In this sheet ( Sheet3) I have the column headings as Day, Month, Year, Date and Submitted By.

Now in the user form I have a command button as Show data (User form Name CmdShow) and Text Box (User Form Name TxtDateLeave) . which I want is that when someone will click on Show data text box will show the data last row value of column D from sheet. Column D of sheet 3 may be Date or Text format.

I have written code for that

Private Sub CmdShow_Click()
With Thisworkbook.Worksheets("Sheet3")
Me.TxtDateLeave.text = .Cells(.UsedRange.Rows.Count, 4).Value
End with
end sub

But this is not working. is there any other way?

View 3 Replies View Related

Display UserForm

Dec 15, 2006

trying to incorporate userforms into my spreadsheets.

At the simplest level, I create a form and display it using

However, I've noticed from some tutorials that the " proper" way to do it is to create an instance of the form before trying to do anything with it i.e.

Dim frm As New userform1

way to initialise a userform? Is there a pratical difference between the two syntaxs or in reality does it achieve the same goal?

Is there something special that I *should* do in the initialise event to allocate memory for the form?

View 5 Replies View Related

Display Pdf In A Userform

Feb 17, 2007

Is it possible to display a Pdf file on a Userform or in a textbox etc. on said form?

View 9 Replies View Related

Display Data On Userform?

Jun 10, 2004

Here to ask for a simple code on how can i display information on a user form once entered from an input box.


1 E12858 Jorge Stregan
2 E112859 Rose Ann


Display all data in a row in userform once any data in A1 entered in input box.

View 9 Replies View Related

Display Results From UserForm?

Apr 16, 2014

I have a userform that simply filters data on a spreadsheet through checkboxes. The form is filtering columns (B through N) to shorten the list of possible outcomes (data in column A.) After the user is done with the form I'd like to populate the data in column A (the results from filtering and there is no way to pre-determine how many rows will have data) with the user pressing a button on the form, into something the user can see without going back to the spreadshet. I was thinking I could populate the results into some field on the form; maybe a list box, txt file, using the camera function in Excel?

View 2 Replies View Related

Display Image From URL In UserForm?

Mar 2, 2014

I have a code that generates a URL for an image (on the internet, not on the local machine) and I would like that picture to be displayed within a userform (preferably at it's original resolution).

View 5 Replies View Related

Copyrights 2005-15, All rights reserved