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
frmMetrics.Show
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 ?
eg
Application.workbooks("Book1")
For the next revision the file may be called Book1.1
Application.workbooks("Book1.1")
View 2 Replies
ADVERTISEMENT
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
View Related
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 rnge.select like this....
set prevrnge = selection
UF.show (vbmodeless)
prevrnge.select
but it doesn't take the focus off the userform....
View 9 Replies
View Related
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()
Range("A65536").End(xlUp).Activate
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
Aug 22, 2008
I have the same problem as Paul C in the following post:
(Pictures disappearing)
"Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i re-open it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear.
I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried re-installing Office 2003 and installing all the latest updates but still get the same problem, any ideas?"
I have tried:
- Tools > Options > View > Objects > Show All
- Changing macro security level
- disabling macro security in my antivirus
View 9 Replies
View Related
Jan 4, 2007
I'm using a VBA UserForm (ShowModal=False) as the front-end on a spreadsheet for logging purposes. Here's the progression of the problem:
1. I have the UserForm loaded and the cursor is in any given textbox/ combobox on the form.
2. I switch to another program, then come back to the UserForm.
3. The cursor no longer appears in whatever textbox/combobox I was in when I left the UserForm. If I type, nothing happens (I have to click the field again first, then type).
However, if I TAB, it will go to the next field in the Tab Order just fine. It seems that the control has "pseudo-focus"--it knows which field to tab to next, but the control won't accept input unless you click it. The odd thing is--this UserForm has a button which launches another "child" UserForm. That "child" UserForm does not have this problem.
View 3 Replies
View Related
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
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:
Sub
' my code has some input boxes before this
userform1.show 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
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'"
'etc...
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
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
May 30, 2006
I have a list of line entries for which I need to insert a variable amount of empty lines per line. (the variable amounts listed in a column to the right of my Active Row.
Unfortunatly I loose the ActiveCell's Value. During Debug it shows the correct cell and value, but after the Debug Step Over, the value shows 0 again. I include my
[code]
Sub IP_Insert_rows()
'
' IP_Insert_rows Macro
' Macro recorded 2006/05/29 by Joseph Clark
'
Dim GrpCtrVar As Integer
Dim LnCtrVar As Integer
GrpCtrVar = 3
LnCtrVar = Range("B8").Value
View 4 Replies
View Related
Aug 17, 2009
i have the following code for sending a worksheet to a closed workbook
View 14 Replies
View Related
May 5, 2006
In the attached file (I'm sorry it is zipped, it was just too big), I gave you a snippet of my database. The actual one has 9000+ records. Over in Y4, you'll see the Sum of all the amounts (column X). Run the first macro, that is, Copy Dups, and you'll see it splits the item up by the numbers in column N. This works perfectly (or so it appears.) Then, run the Summary macro (the only other macro in there) and you will see that it pulls the Sums of each spreadsheet and places them on one nice overview page. The summary page. The only problem is, the total of the subtotals on the summary page do not match the total on the main (unadultered) page. I hope i am explaining this right. Somewhere in the macros, I am losing data from my spreadsheets so that the summary page is not equal to the un-macro'd data.
/scratches head
View 9 Replies
View Related
Jul 28, 2006
As you can see this macro copies a worksheet from one workbook to a new one. It is 14 columns wide by just under 700 rows long. I am losing information out of cells all over the place.
ChDir "H:HOMETIMCEXCELCOUPON_COUNT6_JUN"
Workbooks.Open Filename:="H:HOMETIMCEXCELCOUPON_COUNT6_JUNJUN_2006" + ".xls"
Calculate
Sheets("MONTH_END").Select
Sheets("MONTH_END").Copy
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False .........................
View 2 Replies
View Related
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
Jan 6, 2014
In cell L7 I have (80/60)/24 and formatted h:mm to get 1:20. In cell M11 I have 1:00 (h:mm). In cell M12 I have L7-M11 and get the result 0:19. Why I'm losing a minute and not getting the result 0:20?
View 5 Replies
View Related
Nov 14, 2008
I've got two columns:
A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn
And I need to bring these together in one column so it looks like this
C
abcdef
qwerty
asdfgh
zxcvbn
View 2 Replies
View Related
Oct 20, 2008
I've got a function called countcolor (which I take no credit for) which counts a particular color in a range.
---------------------------------------------------------------------
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
I have then set up a couple of macros which, when ran, fills a range of cells with a particular color. These colors are closely related but as you can see from the two macros below they ARE different. I'm finding my countcolor function is counting them twice though even though as you can see the color values are different.....
---------------------------------------------------------------------------
Sub pegcharged()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 25600...........................
View 9 Replies
View Related
Mar 31, 2009
I need to make an excel sheet with checkboxes (around 50) which each add a value to 1 cell when clicked.
If you click them again the value should be removed again from the cell.
So lets say I got cell A2 and I got checkbox 1 to 5. Checkbox 1 has value 1 checkbox 2 value 2 etc. up to 5.
So if I click Checkbox 1 and 4, cell A2 should display "5". If I check Checkbox 1 off it should display "4". Now I tried to do this but I really cannot get cell A2 to display "4" again (clicking Checkboxes again just adds another value).
I know why this is the case, I defined checking the checkbox as click, not as "check" but I have no idea how to do this correctly.
Little info:
I have little to no experience in VB but got some programming experience next to it. I am using Excel 2007 (forced to, work).
View 9 Replies
View Related
May 22, 2007
This is probably a simple one but I can't figure it out. I am building a workbook from scratch. It has about twelve of thirteen tabs at the bottom. On one of the tabs I would like to copy and paste a stand alone worksheet. The worksheet functions properly when it is stand alone. However when I copy and paste the worksheet into this new workbook none of the formulas transfer with it. I do get the cells filled in, but I would like to get the formulas.
I have done this before and it worked fine. In fact I can open a new workbook and copy / paste the worksheet into it and everything works fine. Again when I copy / paste it to this new workbook the formulas do not transfer.
View 4 Replies
View Related
Jan 29, 2009
I have an Excel 07 spreadsheet containing multiple tabs, modules, and userforms.
In Module1, I have a public variable declared as boolean - will call it X. When X is selected from a combo box in Userform 1, X is set to TRUE. However I've noticed that when the user enters Userform 2, X is somehow set to FALSE.
I can't set it back to TRUE at that point because more often than not, it SHOULD be FALSE, thereby sending the macro down a different path. Any idea how I can retain the "TRUE" value for X. I've tried changing the Public Variables to Global, but am still having the problem.
View 2 Replies
View Related
Jun 27, 2014
I am trying a macro to merge cell without losing the data until I find a next cell with the data - This merging should be pertain only for the column selected. Is it possible to write a macro and preform this activity.
View 1 Replies
View Related
Jan 14, 2013
Is there a way to prevent formulae from losing their references when the references get replaced.
I have formula which refer to a worksheet that is dynamic, meaning that the worksheet holding the data (CONTROL_1) differs from one query to the next is imported from another source. The datasource contents and format are identical.
View 6 Replies
View Related
Oct 30, 2013
My data is in column A , Column B and Column C, where are column C has a uniuqe value for certain rows.
Based on these uniques value, column A and Column B should be merged to singe Row without loosing data.
MY Data:
Source address
Destination address
Destination Port
Application
IP Protocol
192.168.1.2
192.168.250.10
53
dns
udp
[Code] .......
Result expecting is :
Source address
Destination address
Destination Port
Application
IP Protocol
[Code] .......
View 7 Replies
View Related
Dec 4, 2013
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1
123-45678-A-10
123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
Columns("B:B").Select
Selection.NumberFormat = "00"
To produce:
Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02
Now all I need to do is rejoin these 2 columns before sorting. My current code is:
Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value
[Code]...
As many have probably already guessed, this produces the following result:
Col A
123-45678-1
123-45678-10
123-45678-2
I'm looking for:
Col A
123-45678-01
123-45678-10
123-45678-02
**zeroes in BOLD for reference only, result does not need to be bold**
View 4 Replies
View Related
May 8, 2009
Whenever a particular file is saved, and then reopened it says "Error Data Lost" and the entire column that used to have the formulas now has =#NA.
Its only happening in 1 particular column. Anyone know a specific reason this might happen or is something in my code just jacked up?
View 9 Replies
View Related
Jun 27, 2014
How to make a formula with references to certain cells, so I can sort the sheet but keep the formula referring to the same cells as before the sorting?
In the example I illustrated the problem: when I sort the data, the formula refers to the same cells which have now different values, so the calculation is not right anymore.
View 1 Replies
View Related
Jul 3, 2013
sample merging file.xlsx
I have a data array of 7 col and 256 rows. I want to focus only on the first 2 columns though. Here is what is going on. In the first column I have numbers from 1-256, these numbers are merged to the appropriate size to accommodate the data in column 2. However, the data in column 2 is not merged, it consists of 1,2, or 3 lines of text. Excel deletes the text in the rows below 1 if I try to merge, excel does not allow multiple row merge selections. What I am looking for is a formula that looks at column 1, finds how many rows are being merged in column 1, then looks at column 2 and identifies the number of rows needed for merging and displays that text in only one row. Basically, it's a formula that merges multiple rows of text in col 2 depending on the number of merged rows in col 1.
No.Customer/Operator
1Fairbanks Morse /
Beloit, WI
2H+H Umwelttechnik /
[Code]....
View 1 Replies
View Related
Sep 24, 2013
This refers to Excel 2003. I will be dragging contents of cells to another place on the same worksheet. To make myself clear, the text must be moved from the source cells to the destination cells, leaving the format of both source and destination cells - such as borders and fill - unchanged.
NOTE: I know that I can copy the cells and return to the destination cells to delete the content that I want to move. This wastes time when doing many drags and drops.
NOTE: I know that I can paste special, pasting the Values only. Again, this is a lot of extra clicking or keyboard shortcuts. Again this wastes time when dragging and dropping many times.
View 2 Replies
View Related
Aug 3, 2012
I have 4 different workbooks that I want to merge into one workbook. I tried moving the sheets from these workbooks into one workbook but to my surprise all the vba / macros didn't transfer over to the other workbook.
I searched here but all I can find were people that wanted to make a summary workbook or merge workbooks into a worksheet which isn't what i want to do.
I just want to simply merge these workbooks into one workbook without losing all the marco and vba stuff that are in each workbook.
View 2 Replies
View Related
