VBA To Import A Couple Of Userforms
Mar 15, 2003I am trying to create a new workbook out of an existing one by exporting three sheets. I need to bring along a couple of userforms.
View 9 Replies
ADVERTISEMENT
Import Modules & UserForms From Workbook
Sep 17, 2007I am basically after a script that can be run from a persons workbook via an Excel Addin, e.g. they click an "Update" button, and the script imports the Forms and Modules from another workbook that i specify.
I do not however want the data held on the spreadsheet to change, just the Modules & Forms in the VBA project.
Select A Couple Of Cells In A Row
Dec 1, 2009I would like to write part of the code that select a couple of cells in a row (for example : Range("A1:A10).select) than:
-find a text which is in one of these cells (for example "HP") - and gives me back column in which this text is input
texts in selected cells will always be the same (for example : HP, CH, SD, .... ect.)
Finding An Average Couple Numbers
Jul 9, 2008I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.
I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.
Create Couple Of New Worksheets With Existing One
Jul 7, 2014I want to create 4 quarter sheets at the same time, but my code could only process 1 quarter sheet each time. Also, I have trouble of copying and pasting the values from yearsheet to the quarter sheets. ( see attachment document )
View 5 Replies View RelatedSum A Couple Of Different Types Appearing In A Column
Dec 10, 2009to create a macro that counts a number of values for a couple of types. The list looks like this;
29 bananas
13 apples
18 bananas
14 pears
7 pears
etc.
So i want to create a loop the goes through the list and adds up all bananas, apples and pears. How do I do that in the best way?
Deleting The Rows Based On A Couple Of Variables
Nov 25, 2008I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.
I need this deleting of rows based on a couple of variables.
I need to keep any row that Col F has the text "QCCONTROL"
and
I need to keep any row that COL N has the text "HOLD"
and
I need to keep any row that COL N has the text "REJECTED".
Sub Delete_OK_Lots()
lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 2 Step -1
If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then
Sheets("adage inventory").Rows(x).EntireRow.Delete
End If
Next x
End Sub
Merge Couple Of Spreadsheets With Common Column / Field
Sep 15, 2013I have a couple of spreadsheets. all of them have a same or common column called "ID".
The order of the ID columns of the spreadsheets are not in the same order however.
I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.
To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
Determine Latest Price Based On Couple Of Criteria
Apr 17, 2014I have actual date , then I have names of customer and I have product reference. And what I want to find out is the latest price COLUMN D for which this specific product was sold to specific customer (Data in rows E,F,G,H are from another tab). So basically I want is to find out that latest price of product x27 sold to customer A on 15.7.2013 was 70 because on 12.7.2013 we have sold it to customer A for 70.
A
B
C
D
E
F
G
H
[code]....
Randomly Select Cells Based A Couple Rules
Nov 6, 2007I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters.
So what I am doing is having excel designate equal amounts of playing time for each player each game.
Is this possible.
Can I base this off of previous games?
Spreadsheet For A Couple Hundred Rows Of Data And 6 Columns
Jan 4, 2010I have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows
Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.
How To Change Absolute Cell Reference Based On A Couple Of Conditions
Apr 28, 2014I have a workbook that contains a column, G, which includes a formula that calculates distances (based on lat, long coordinates) to a training site ("TS" going forward) from other sites within the same district as that TS. There are numerous TS's in the company, usually 1 per district (but some districts do not have a training site), and I am trying to find a way to change my distance formula that will automatically detect which site is a TS, and then calculate the distances of those other sites in that same district to their respective TS's.
As it stands, I have to manually change an absolute cell reference every time I encounter one of these TS locations in the file (designated by a "TS" in Col F), and rerun the distance formula down the column. I would hate to have to do this several hundred times.
Attached is a sample file. Again, Col G contains the formula for distance calculation, and you will notice that the absolute cell references change each time a TS is encountered -- I have been doing this manually. Additionally, I would be curious to know if there is a way to spot any Districts that lack a TS, as in District 493, and specify that in Col G.
Formula To Comput Hours:minutes Between Time On Different Dates A Couple Of Days Apart
Oct 14, 2009Im trying to figure out the formula to comput hours:minutes between time on different dates a couple of days apart.
B12 = Date
B15 = Time (on date in B12)
Previous date and time are in AA9. I have been trying to us =SUM(B12+B15)-(AA9).
Import Multiple Text Files & Copy Each Import
Jan 29, 2008I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Import CSV Every X Minutes Without Import Dialog Appearing
Apr 1, 2008I have a simple macro importing a .csv data file to a worksheet every 30 minutes.
When the macro runs an Import Text File dialog box displays.
What do I need to do to have the macro run to completion without the dialog box interruption?
Sub RefreshHourlyData()
htime = Now + TimeValue("00:30:00")
Application .OnTime htime, "RefreshHourlyData"
Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A1:A1").Select
End Sub
Copy Worksheets From A Couple Of Workbooks To Other Workbooks
Jun 26, 2014I have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
Userforms Not Hiding?
May 28, 2014I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
Application.ScreenUpdating=true
Waitingform.Hide
If And Then Statement In VBA For Userforms
Jun 9, 2014I have created a userform and command button within an excel spreadsheet to run the userform and then manipulate the data entered into the userform. However when I click the cancel button of the userform the macro tries to continue to manipulate the data entered, of which there is none and then comes up with an end/debug command. Is there an If and Then statement in VBA that I can use so that if the 'Cancel button' is clicked then the rest of the Macro won't run.
View 14 Replies View RelatedPop Up Boxes In Userforms
Jun 29, 2007I designed some userforms on a worksheet for users to click and input information. However, i am wondering if it is possible to have an additional feature to the userform.
Currently the userform only have labels such as Title, Dept, Branch, Unit and the respective textboxes for user to input information. As it might be unclear to some users i would like to have an additional feature such that when user roll their mouse over the label "Branch", the user will see a box where more information is given or rather an example such as "Dept XYZ", allowing user to have a clearer idea of what to fill in. Is there any way for me to add this feature?
UserForms & ListBox
Jan 23, 2009I have a UserForm which has a ListBox and other TextBox, I have an issue when I select any option from the ListBox. I transfer data from the Form onto a Worksheet, which works great until I reach the ListBox.
My problem is that when I enter the information and click submit which will save the Data and send it to an Access Database. The problem arises on the ListBox. I get an error message Run-time error 3265 "Item not found in this collection"
I dont understand what the error is, how can I get around this so that all the data is written to the Worksheet and can then be transfered to the Database?
Add Comments In Userforms
Oct 30, 2009I was wondering if there was a way to add comments (that you see in spreadsheets) on a userform in vb editor. So that when you run the form and the user moves the mouse over a textbox then it displays the comment.
View 2 Replies View RelatedVariables In Userforms
Jun 11, 2007I have a sheet with two user forms. One which collects a few options and a second where I use a webrowser and some more options on it to do some tasks as per the options selected in number one.
I am having problems because I need to use the options that are selected on the first user form and apply them on the second userform by using if statements.
So basicaly the question is how can I use a variable defined in one userform and apply it on another userform?
Userforms And Checkboxes
Jul 10, 2007I'm having trouble with checkboxes - I've set up the userform but I can't seem to get it to send values to the specific cells when certain options are checked.
The way it should work is there will be an action and this could be involved with 1 process or 40 processes (and anything in between) and what will happen is the userform will pop up and ask what processes the action is applicable to. The user will then tick the correct ones and the form will then place these values in the corresponding cells next to the action.
Starting With UserForms
Jan 24, 20081) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?
2) How do I get the options from the userform to the worksheet?
3) How do I put an input box on a user form?
Hiding UserForms
May 7, 2008I'm trying to hide a UserForm (using myFrm.hide) once the command button on the form is clicked. I'm getting the following error:
Run-time error '402'
Must close or hide topmost modal form first.
Apparently the form isn't the 1st in the z-order. How can I see the z-order of my forms?
Multipage On Userforms
Jun 24, 2008I have had some succcess with Userforms, but I have now tried creating multipage ones.
I assumed that when I clicked on page 2 or page 3 of the multipage control, it would take me to a blank canvas, but instead it just replicates what is on Page 1. If I put textboxes and labels on page one, it repeats them on pages 2 and 3. If I delete boxes fropm page 3, they also disapear from 1 and 2!
So im unsure how to put different labels etc onto different pages.
Multipages In Userforms
Jul 16, 2008I have an easy question regarding the Multipages in userforms. If I want to use Multipages, do I simply create a new userform, then select "multipages" from the controlbox and distribute all over the whole userform ?
View 9 Replies View RelatedCannot Access UserForms In Add-in
Jun 29, 2006I would really like to be able to have a function in a workbook that can run methods (for example Show or Hide) on a UserForm inside an add-in like this:
Public Sub Test_Addin()
frmTest.Show False
End Sub
where frmTest is a UserForm inside the addin. After the add-in has been added as a reference, I am able to access all of its functions/subs in this way. When I try to access a form inside the add-in from outside the add-in, I simply get an object not defined error. Looking more closely, if I type in "eRFTEAddIn." (which is the name of the add-in) and look at the possible options, the forms do not show up...although the modules and sheets do.
Listboxes In Userforms
Jul 25, 2006I am creating a userform with a listbox.
When I select multiple values from the listbox, how do I apply the selected set of values (user can select up to 10 out of 80) to a range of cells in a different sheet (in the same workbook), which cannot contain any blank cells?
100% CPU Usage With Many Userforms
Nov 21, 2006We have designed a data collection program in excel which use's userforms for the data entry and form printing. There are 20 userforms, each with a "Next" button which hides the current userform and shows the next userform. The first "next" button has a copy and paste code which copies a today() statement and paste's it in a different cell to record how long it took to complete the data entry. We are able to go through the program once, but when we try a second time it freezes and the cpu usage goes to 100% resulting in us having to End Task.
View 6 Replies View Related