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.
I am using MS Word to create a list to go into Excel, it seems advantageous to do it this way because I'm using the Heading Outline Numbering feature of Word.
Presently my macro assumes the word document is closed. How do I modify the macro to:
1. check if the file is open? 2. if already open, switch the focus to word? 3. switch the focus back to excel?
Sub GetDataFromWordDoc()
Dim FileToOpen As String
FileToOpen = ThisWorkbook.Path & "5Whys.doc" Sheets("Data1").Select ' MsgBox "Active sheet is " & ActiveSheet. Name ' My check ActiveSheet.Range("WordClear").Select ' Existing data to remove Selection.Clear
Set appWD = CreateObject("Word.Application")
' here I would like to check if the word file is open
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")
I have a macro that launches another program (Windows Title - Storm). I then use sendkeys to have it export the file to an excel file. (This launches another instance of excel.) I want to be able to focus back to (Storm) so I can sendkeys to close the program down. I can get it to the end result using sendkeys, but I am trying to have it switch back to the Storm application so the user does not need to close it manually.
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
If the admins or you would like to change or recommend a change, I am trying to figure out what the code would look like for cells B22:B28. I need it to count the number of occurrences of the part number in A4:A17 and then combine the applications from the corresponding cells in column F (F4:F170 into a single cell (B22:B28).
On sheet 2 I would manually copy/paste the list of part #s from sheet 1 and remove duplicates. The add the formula that I can't figure out into column 2.
Passing an array from a sub to another sometimes works, sometimes not. But when I call the same sub twice, it definately gets stuck at least at the second time. Seems as if I tend to lose some of my items of the array. And I cannot figure out why it happens, at what pace it happens or how I could stop this from happening.
1.Could it be that a control perishes as soon as it is set to 'ctrl.enabled=false'? 2.Or could it be that a declared array 'ary=array(tb1,cb1,frm1)' can not be handled without having had its items initialized with some lines of code, although the items visibly exist within the user form? 3. Can it be that a Sub 'remembers' the former values passed to it, if set to 'optional'?
However, I have added a cleared/minimized version of my userform as a file to this post, hoping that some of you out there might find the time to have a look at it.... I would really appreciate it. I have added the decisive parts of the macro below here, as well,
Private Sub UserForm_Initialize() Dim elem As Variant Dim wSht As Worksheet Dim ctrl As Control 'set control arrays '[...] ctrlAllStep2 = Array(cbTimeOnly, cbIntensityOnly, cbAreaOnly) '[...] ctrlAllStep3 = Array(tbIntensityTotal, tbIntensityBleached) '[...]
MsgBox "Two Controls exist now: 1." & ctrlAllStep2(0). name & ", 2. " & ctrlAllStep2(1).name, vbInformation, "Note: controls exist..." 'disable Controls in Step2 and in Step3....................
is there any way to know if a COMBObox has the focus?
* * *
I have a ComboBox and when I press ENTER inside this comboBox a run a Macro
Private Sub CbFormularios_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
some times I am inside the ComboBox and one of the Item whithin this combobos is Selected, but stead of pressing the enter keyboard I press a commadButton but to run the macro attached to this command button I have to know if the cursor of my mouse is whithin the ComboBox "Over the comboBox". kind of:
If ActiveSheet.Cbox1.Value Is Not highlited Then call Macro1
I had to reset my excel on friday now today I'm getting an error when opening up an workbook. The error is " Cant move focus to the control because it is invisible ,Not enabled or type that does not accept focus. I have this code in a workbook when the workbook opens
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.
I'm trying to set the focus back to the field that triggers an AfterUpdate event.
Private Sub myField_AfterUpdate() myfield.SetFocus End Sub
Code above sets the focus to the next field in the taborder instead of keeping the focus on the field (that didn't pass the validation) that triggered the AfterUpdate event.
I need to test what control will receive focus after the exit event this test needs to be in the exit event so i am able to validate and cancel only if focus will be the enter button
Private Sub myTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean) If NextControl.Name = "EnterButton" Then DoMyValidationTest 'and other stuff, such as set TabIndex Else 'do nothing End If End Sub so what should "NextControl" actualy be???
I am trying to run multiple applications. I can run one application i.e.
Application.Run (Sheets("Sheet2").Range("A8").Value) but not multiple i.e Application.Run (Sheets("Sheet2").Range("A8").Value) & Application.Run (Sheets("Sheet2").Range("A9").Value) at the same time.
I have also tried Application.Run (Sheets("Sheet2").Range("A8:A9").Value) but to no avail.
A VBA macro uses the Shell command to open several DOS command boxes. Each DOS command box is copying a file from one location to another. Since I am copying the files over a network, it is most efficient to copy the files in parallel (simultaneously) rather than sequentially. I would like to pause the program until all of the files are copied, i.e., until all of the DOS command boxes have closed. Is there a way to pause the program until all command boxes are complete?
I have a macro that cycles every 5 minutes. I have tried everything I can to get this program to run while I modify a sheet in the workbook to no avail. I have however figured out that if I open up a separate excel application that my macro can continuously run without preventing me from modifying the other application. Now I need to figure out a way toggle between the two excel application windows if a condition is met. The basis of this is to allow production associates to create a schedule and modify it while a macro looks into the defect database and if a defect becomes too problematic, the macro interrupts the schedule to display the issue.
Is there any way to access all the applications which are opened through only Excel.. Like creating a menu which will list all applications that are running.. like Word., other excel workbooks,Notepads etc?
so i'm building an application that'll allow users to manipulate records in Excel with just a GUI, using Userforms, Modules and Class Modules. it's all working but i'm feeling like i skipped a little on structuring it properly.
for example (from my Java work in college), you'd call just one object from the main method, which would create a GUI object, and create/manipulate instances of the different Classes when buttons are pushed. basically you one object whose main created other objects, who ran procedures, etc. what i'm hoping for is to make it as modular and easy to maintain as possible. would anyone have a good resource for optimising a medium-sized application? (the tips Excel/VBA Golden Rules. These Should NOT Be Optional were very good, by the way.)
programPath = "C:Program FilesInternet Exploreriexplore.exe" ' works 'programPath = "iexplore.exe" ' does not work Shell programPath + " " + fileToLaunch, vbNormalFocus
but the drawback is that the invoked program (iexplore.exe, at least in my case) needs to have the FULL PATH to where the program exists = the "C:Program FilesInternet Explorer" which may or may not work on someone elses computer. This hardcoding will not work and is not transportable.
Is there a trick to find where the executing program lives? or launching it without the path?
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
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
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.
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?
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...........................
