I have 3 Databases for three different departments, namely Finance, Services, IT. These 3 databases are housed in the same workbook - Sheet 1(named Finance); Sheet2 (named Services) and Sheet3(named IT).
They each have the identical columns and column headings, but I have to maintain them for each of the departments.
I created an Excel UserForm to enable me to input data onto the first database (i.e. Finance). In the VBA editor, lefthand side, it has the name 'frmFinance', and all the codes are pasted on the righthand window of the 'frmFinance' sheet.
Is there anything I can do to be able to use the same UserForm to enter data onto the other two databases without having to create a new UserForm for each one of them. As indicated above, the three databases have identical columns and headings, and the data entered onto each of them are identical. The only difference is that they are for three different departments and I have to maintain each one of them - albeit in one Workbook.
I have a "form" type spread sheet that I have created where the user can enter different information (like name, company, addresses etc.). I have figured out how to create a drop down list. But what I would like to do is create on a seperate sheet a "data base" of information which will be "copied" into the appropriate cells on the first sheet depending on what the user selects from the drop down list.
The drop down list will list company names, which are stored on the second sheet. However, each company will have other data such as the company address (which will be entered in seperate cells with information like, box, street, town, province, country, code).
Now this is the clincher... The user must be able to add to the data list on sheet two and any new entry must automatically appear on the drop down list on sheet 1, and when selected it's "data" must be transferred to the appropriate cells on sheet one from sheet 2.
I've created several access databases to which I've connected my excel files. However I've either deleted/moved these databases but my connections are still showing in Data -> Existing Connections tabs.
I've tried going to C:Program FilesMicrosoft OfficeOffice14QUERIES but no such queries exist.
I have a dataset from Oracle that includes a study patient ID, study visit that a UC sample was collected at, date the sample was collected, and sample type. I also have a dataset from Quest, where the UC samples are sent to for testing, that contains similar information.
I need to reconcile this data, making sure that patient IDs, study visits and collection dates all match up with out using just vlookups and having to check the reverse of each (vlookup Quest into RDC, then vlookup RDC into Quest) and I've been wracking my brain trying to figure out VBA code to get my data from Oracle (orange header) to merge with Quest (blue header) and merge on Subject ID and Visit Name only.
In exhibit C, this is what ideally I would like to see as a result, data points from both are reported, but if Oracle subject id = Quest subject ID and Oracle Visit Name = Quest Visit Name, they are reported in the same row. I can then just run a simple formula to compare the collection dates and also identify discrepancies between visits (example: exhibit C, rows 10 and 11, these two UC samples look like they should reconcile because the collection date is actually the same for both, however either the study site or Quest is reporting the wrong study visit name).
I have three very large columns that I need to deduplicate and dump back into three separate email campaign databases.
Here's the scenario: First column: Largest (15,000+ records) Second column: Medium (10,000+ records) Third column: Small (7.000 records)
I need to know if the first column contains dupes from the second & third columns. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 1. Then I need to know if the second column contains dupes from the first and third column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 2.
Then I need to know if the third column contains dupes from the first and second column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 3. I can't just dump all of them in one column and auto filter on "unique records only" because the three columns MUST remain separate from each other.
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click() Sheet7.Activate Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .RightFooter = " Construction Assumptions" .PrintArea = "CONSTRUCTION" 'the named range to print .Zoom = False .FitToPagesTall = 1 .FitToPagesWide = 1 'this changes depending upon the page selected End With ActiveSheet.PrintOut End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate 'Sheet3.ResetAllPageBreaks 'disabled due to errors Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .PrintArea = "DEVBGTALL" 'the named range to print .FitToPagesWide = 4 'this changes depending upon the .FitToPagesTall = 1 End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I am creating a userform that has multiple calculations in it. I understand how to do this in Excel but I have no idea what the order of operations would be with a user form. (UoM Cost) will = Unit Price / Quantity). The (Ext Cost) field would equal [Quantity x Waste% x UoM Cost (that needs to be calculated before) + Unit Price. (Cost Per Each) would = the "Ext Cost"....that needs to be calculated before / Quantity - Waste%.
Once I have these calculations, then need to click a button to either reset the user for for another record (or if I mess up) and a button to save the record to the excel spreadsheet in the background.
I have workbook with 12 sheets in it. each sheet is identical except that they are for each month of the year. i am using a userform to enter the data into each sheet. Each sheet will have different data.
I want to be able to use the same user form on each sheet but still have them insert into the open sheet. Currently I am creating duplicates of the userform for each sheet but it gets VERY!!! tiresome and it is making the workbook VERY large (5 mb so far). For example. the first sheet is "april". i open the userform and enter the data into the userform. the userform enters the data into the sheet.
now i go to sheet "may". currently I open a userform in that sheet that looks exactly the same as the userform in sheet "april" but is a completely different userform. I want to be able to use the userform from sheet "april" in sheet "may" but when in sheet "may" it inserts into sheet "may".
I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.
Private Sub UserForm_Initialize() Dim MyUniqueList As Variant, i As Long With Me.ListBox1 .Clear ' clear the listbox content MyUniqueList = UniqueItemList(Range("o4:o100"), True) For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i .ListIndex = 0 ' select the first item End With End Sub
Private Function UniqueItemList(InputRange As Range, _ HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula <> "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UniqueItemList = "" If cUnique.Count > 0 Then Redim uList(1 To cUnique.Count) For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItemList) End If End If On Error Goto 0 End Function
I have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches, however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match. Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas). (attached is a sample worksheet)
I have created this with text, command & list boxes with a view to creating an asset register.
So the options i have are premises, hardware, software & fixtures. I have tested so that these go onto 1 sheet ( at the moment all additions go to premises) but i require for it to go to there respective sheets i.e. a sheet for each asset.
But, due to inexperience with userforms and VB i don't know how to do this.
Also, how can i get a sequential number coming up for each asset on their respective sheets
Is it possible to create a data base with the given sample below?
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E
UNIT TYPE CONDITION NAME TELEPHONE NO. DATE
1+1 SALE Name1 tel1 3-Oct-12
[Code] ...
The Userform has 2 ComboBoxes and 1 ListBox
ComboBox1 will be the one of the Column A Value ComboBox2 will be the one of the Column B Value
I did handle that ComboBoxes already....What I need only, when those ComboBoxes been selected the related datas will fill into the ListBox accordingly...
I'm trying to set up a small database using excel ... utilising a userform to populate the database.
I have a userform set up with the following fields:
Bike Odometer Date Task1 Parts1 Cost1 Task2 Parts2 Cost2 Task3 Parts3 Cost3
What I would like the form to do is create rows based on the task, and generate rows of data as per the example below. For the 'activity' the bike, odometer and date are common, and a new row is created for each task.
In this example I have three tasks/parts/cost in the userform, but I plan on having around 20ish (xheck boxes, combo etc) ...
I have a workbook with 250 worksheets and I'm trying to build a single userform to work with all of them and I'm using binding to do that. Reason for using binding is that information is scatered all over the place, I mean a single column contains more then five different types of data with blank columns left in-between to be used as seprator, I know it's one of the worst ways to arrange data. Anyhow, the best part is that all the worksheets have the same type of information in their cells as of any other worksheet so binding does seems to be a good way to go ahead but only if it's dynamic enough to change accordingly with the worksheets.
Now I have already created a navigation-bar for it so I can use the same code for a drop-down menu list but what worries me the most is can I able to use a single form for all the worksheet and could it be done through binding?
I have a form using in Excel 2003 that's 5 columns and 20 rows I need to populate the Textboxes with values from a worksheet. I want to use a loop statment that fills the first row, then increments to the next row until all 20 are filled. I have named each row the same name except the last character is the row number 1-20.
Sub test() Dim RowNumber As Integer Dim FormRow As Integer Dim NameRow As Object Dim AddressRow As Object Dim CityRow As Object Dim StateRow As Object Dim ZipRow As Object RowNumber = 3 'Row in Data sheet FormRow = 1 'Row on form NameRowString = "NameRow" 'first part of the named object Do While FormRow < 21 NameRowVar = NameRowString & FormRow................
I created a userform. Now, I want to make a sum of the textboxes in my userform and pop up a warning signal is the sum isn't 18. I tried it in different manners, but I always get some problems. I entered default values of 0 in my textboxes. When I edit the values by using the userform, the popup message will appear even though the sum of my textboxes is 18. Here is the code for the pop up warning message.
If (Me.txt1.Value + Me.txt2.Value + Me.txt3.Value + Me.txt4.Value + Me.txt5.Value + Me.txt6.Value + Me.txt7.Value _ + Me.txt8.Value + Me.txt9.Value) <> 18 Then MsgBox "Vous avez fait une erreur. Le total ne donne pas 18 trous." Me.txt1.SetFocus Exit Sub End If
I'm designing a form to look at data contained on a spreadsheet. I aiming for the user to select filter criteria which will then be used to reduce the data based on the criteria selected using autofilter. How can I set the filter criteria using a form to give the same options as when you manually select the filter using the arrows at the top of the row? Can I get the filter selections into a list box?
I've made a quick example file ( not as complete as my original file ) But they have the same code.
What i wat is the OrderNr. textbox in the userform to be filled in automatically. When customer 1 orders something new the OrderNr. needs to be 3.
So i've i click on the userform launch button ( command button 1 ) I get my userform with 2 textboxes and 2 command buttons What i press command button 1, i get a new form to select a customer.
Now the part that's missing:
If i press command button 2 i want the textbox of OrderNr. to be filled in automatically with a 3.But it has to be changeable. So if i hit backspace its empty again and i can type 4 ( or what ever ).
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background: -- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook -- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing. In the entire workbook, I want to add 1 (counting function) only when: R1=2 AND E1>F1 or S1=2 AND G1>H1 or T1=2 AND I1>J1 U1=2 and K1>L1 on each appropriate sheet in the workbook.
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no product sales qty total
[Code] .....
I want to display the data in another sheet in the following format:
