Programmatically Built UserForm Doesn't Always Remove Itself?
Nov 7, 2011
I have wirtten a sub routine that builds a userForm dynamically based on user specific data. The form builds just fine and 9 times out of 10 the code at the end of the sub routine removes the form from the workbook. Since it's built on the fly I don't bother renaming the form. The times when it does not remove itself and the user restarts the program, part of the code written to the forms module references "UserForm1". and the the code halts. I know there is a way I can verify that UserForm1 has been removed and if not remove it before executing the next sub routine, I'm just tired and can't remember anything. Note that the UserForm1 contains CommandButtons that direct the user to Static UserForms that do not get removed, and those can trigger the sub routine to rebuild the dynamic UserForm.
Suppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are:
I'm trying to programmatically add and remove the Outlook 11.0 Reference Library in Excel 2003. So far, I have found the following code which successfuly adds the reference:
i m working on an excel 2007 workbook with ms project functionality. in order to be able to use ms project from within excel i m using early binding. for those who dont know what early binding is: http://www.dicks-clicks.com/excel/olBinding.htm
this works fine if the user has ms project installed on his/her pc.
if the user does not have ms project installed i remove the broken references. this works fine.
but my problem is that when i remove the reference to ms project, my vba project will not compile correctly. thus each time when a user opens the workbook the user gets a "compile error in hidden module" , since the functions of ms project are not available.
for example i get a compile error in the following source Public Function getResID(ResName As String, ActiveProject As Project) As Integer Dim res As Resource For Each res In ActiveProject.Resources If res.name = ResName Then getResID = res.ID Exit Function End If getResID = -1 Next End Function i just want to know what i can do to prevent the compile error? would the use of late binding remove the problem? actually i dont want to use late binding since my source code is already very complex and it would be a huge effort to change it.
I have a userform that after some operations won't unload. It executes subs from other buttons on it but it won't unload. I use ScreenUpdating but I always set it to true.
I have a number of similar templates on a server used to produce quotations from other files with lookup formulas. They all have 2 modules, 3 & 4. Module 3 deletes certain data and shows values instead of formulas for most of the pricing etc. Module 4 contains a macro that logs info in another central workbook on the server.
If 2 files are opened based on the same template at once, when the Quote_Wrapup macro (in module3) is run on one of the open files(code follows) from a button on the spreadsheet it often produces a Run-time error '9' Subscript out of range error.
I have multiple Userforms with labels etc, all controls are similarily named (so I could do this) and I can refer to the Userform dynamically but the Label caption/Controls don't change, any idea's how to get them working.
Sheet 1 is a sheet that has a shape with macro assigned to show Userform6.
Sheet 2 contains data that is displayed on Userform6. I use Userform6 to show statistical data that is on Sheet2.
Sheet 3 contains data that is input to ComboBoxes on Userform6 using the RowSource Property.
On Sheet 2, when I select Userform6, the data is correctly shown in all comboboxes and Textboxes......
On Sheet 1 when I select Userform6, only the comboboxes show the data...the textboxes do not show any data.
I do not want users to see the data on Sheet 2 which is why I want to show the Userform on a different sheet as the data sheet will be hidden from users.
Question is: How can I show Userform6 on Sheet 1 with all the data showing in textboxes?
I have this formula =COUNTA($A:$A)<=4 that limits amount of cells that can be populated in column A, I use data validation with "Allow costume" option and using that formula. It works fine from worksheet it displays the message when the limit is reached but it doesn't work when data is inputed/populated from userfrom, it allows userform to put more entries than set limit 4 in this case.
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet() Dim ColNo As Integer Dim rng As Range Set rng = ThisWorkbook.Sheets("Week2").UsedRange
I am using a user form to display a splash screen. In the user form, I have a picture that is being displayed as splash screen for 5 seconds and then it disappears.
I only want to display the picture in the splash screen.
I want to to remove the following three items from the user form:
1 - remove all borders 2 - remove the menu/caption bar 3 - remove the "x" button that allows user to close form.
I have a function (courtesy of Walkanbach) to create a userform, very useful as it means I keep memory down as I can create the UF's as I go and tweak to suit.
I have been trying to get the userform to be removed when the user closes it. The close action in this case is the X button top right as this userform only has the userform itself and a listbox.
When I set the code for the event procedure it doesn't error, but nothing happens. The microsoft help pages seem to suggest that you can't remove a userform through vba, but I find that hard to believe. I've tried variations on the close procedure and all to no avail so far.
Dim xInt As Integer With TempForm.CodeModule xInt = .CountOfLines .InsertLines xInt + 1, "Private Sub TempForm_Terminate()" .InsertLines xInt + 2, "ThisWorkbook.VBProject.VBComponents.Remove TempForm" .InsertLines xInt + 3, "End Sub" End With
can i disable/remove a control from the being in the tab order. i no i can move to the bottom but there is too many too use that option. i need it to be elimated from user input alltogether. it is filled automatically
it is possible to hide empty cells when using a ComboBox in a UserForm?
I have named the range from where I select my data and used the RowSource (ComboBox Properties window) to determine the selection. This works perfectly but I have many lines that are empty and I would like to know if they can be hide, or not selected, in the combobox.
UserForm1 Current OptionButton names are like ABC123, ABC 124, etc.
How can i change all names, with code - as i am not seeing a find and replace option within UserForms, to all but the "ABC" part, the result of the OptionButton names shall therefor be 123, 124, etc. - delete "ABC" or, find "ABC" and replace with
I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.
I have the data below. I need to total the amount in the USD Equivalent column but there is an exception. Where the "Curr Sold" and the "Curr Sold" are the same in reverse (as highlighted) in yellow, I need to take the net figure. For example below, I need to add the USD Equivalent column but then minus the 6,013,072.66 because the CHF - USD is the same in reverse with the same maturity date.
I am looking for the settings if possible that will add to a custom tab in the ribbon.
What I have is an add-in that creates a custom tab in the excel ribbon. I save it as an add-in and activate it in excel options so it is available for all workbooks.
Now I have a callback in there that runs a macro that opens a workbook, this workbook also has custom UI that I want additional callbacks added to the same custom tab created in the add-in.
I need to modify 2 Excel built-in dialogs via VBA. Here is the first dialog box:
Application.Dialogs(xlDialogWorkbookCopy).Show
First, in the "Move or Copy" window, where it says, "move selected sheets to book:" I'd like the default selection to be the open workbook instead of "(new book)", which is the current default. How can this be done?
Second, I'd like to remove the check box so there is no option to copy visible, however, I need to add the code in the sub procedure to make a copy. How can I do this?
The second dialog box I need to modify is this one:
Application.Dialogs(xlDialogPageSetup).Show.
What I need this to do is open with the tab" Header/Footer" visible as a default, or, better still, have only the "Header/Footer" tab available and the other 3 tabs not there at all. How do I do this?
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
I would like to export some of the functions available in Excel/VBA to some other languages (especially lookup functions, COUNTIF, etc.). Is it possible to find the code somewhere?
I am attempting to create the Name "L1" for a list of cells but it conflicts with the built-in name for Cell L1. Is there any way around this if I need it to read "L1" exactly, and not "L_1" or a variation?
I am trying to assign a macro to the Print button on the Standard Toolbar. Basically I want that button to print the number of pages I've specified in a cell, but I still want to be able to use the 'File/Print...' option if I need to.
I did try to solve this issue in an earlier post, but this exact problem remained unsolved. For more information
I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.
I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.
