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.
im in the process of designing a userform but it needs to have some calculations in it, and im lost on how to do it if anyone can help
ie
in textbox1 there is a value of 5 and in textbox2 there is a valuve of 10 and in text box 3 i want to appear textbox1 multiplied by textbox2 so 50 should appear
this is a basic example but as soon i have mastered that i can adapt everything else to my userfom
will the above be done automatically as soon as i enter values or would i have to "make the calculation" via a button or something
and also i want to have a enter button or something like that that copies the data that i have put in the various text boxes to the excel spreadsheet and clear the userform cells
phew thats best i can explain any questions just reply to this post as per the norm
I'm currently working on the database for use at work and i would like to improve the code below with couple more functions in my userform.
This code is triggered after i put any value in the textbox then it looks for part number in the "Update" tab and counts how many times this part number appears in column ("Q2:Q") and then depending on count result it shows nothing or "No Sample" in the "Skip" label.
I would like to add some improvements to this code and here comes complicated part:
After i will input any value into the textbox the code should:
1. go to "Database" tab and sum all majors and minors for this part number; Part numbers are listed in the "Database" tab column "F2:F", Majors in column "P2:P", Minors in column "O2:O". Majors and minors are numbers.
2. if this part number has no majors or minors (basically if all cells in column P and O are blank for this part number) proceed with the code below otherwise substract (-) the sum of majors and minors from the count result in the "Update" tab.
For example part number "PN1234567" has been booked 10 times(counts in the Update tab column Q) and we summed 3 majors and no minors for this part number (summing in Database tab columns P and O) it should look like this : 10-3=7, basically we need to do this simple calculation. Of course if we will find more majors and minors then the bookings it should look like that: for example 15bookings-10majors-10minors = -5
3. and finally knowing the final result we can select the case as in the code below.
I have a spreadsheet that I am trying to have automatically calculate a total based on certain criteria:
I want the amount under the per diem amount column W to return a value based on if P is entered in column V. If this is for partial it will depend on departure and arrival times. If departure is after 6:00 AM no breakfast per diem is paid, if departure is after 11:00 AM no lunch is paid. If arrival is before 2:00 PM no lunch. If arrival is before 7:00 PM no dinner. If the user enters a 1 or 2 in column U, 1 uses out of state per diem breakdown located in cells I38:I40. In state uses a 2 and is located in cells E38:E40.
I want to pay different commission rates for different levels of sales...
IE nothing if sales are under 250,000 per year. 5% between 250,000 and 500,000, 8 % between 500,000 and 750,000 10% between 750,000 and 1,000,000 12.5 % between 1,000,000 and 1,250,000 and 15 % over 1,250,000
The issue that im having trouble with is that if the sales guy brings in 1,500,000 in yeary sales he would be paid some at 0%, some at 5% some at 8% some at 10% , so at 12.5 and some at 15%
How do i calculate that? I have included a excel spreadsheet.
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them.
for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
I have a start time and a stop time, then a difference is calculated in cell L53. I want to subtract the time value of a break but it only works if the entered value is greater than 1:00. I tried K2-I2-L55 and It works but It wont work for times less than 1:00. I need to be able to subtract :15, :30 or any other value in cell L53.
I am trying to track the time the various activities take. I need this in hours:minutes - Monday through Friday. Attached is the spreadsheet that does NOT work. An "x" in the column creates the timestamp in the adjacent column. Some of my formulas are not correct as the calculations do not create the correct data.
I'm working on this excel tool that I would like to make as user-friendly as possible. It would act like a survey almost where the user would choose responses or input values for specific questions and then on other worksheets it would have the actual calculation and math.
I see that if one were to add a value onto worksheet 1 and which would be a variable that is essential to a calculation on worksheet 2 it does not run automatically.
If there is a combo box w text values that also needs to have a calculation on another page, can i just do an IF statement on worksheet 2 with combo boxes?
I have one spreadsheet that contains employee overtime info per pay period which is bi-weekly, another spread that has their monthly production numbers. So...
Employee OT PayEndDate John Smith 5 01/13/12 John Smith 7 01/27/12 Jack Jones 8 01/13/12 Jack Jones 9 01/27/12 John Smith 6 02/10/12 John Smith 3 02/24/12 Jack Jones 8 02/10/12 Jack Jones 10 02/24/12 And so on
On the other sheet Employee Production Month Jonh Smith 53 Jan Jack Jones 75 Jan John Smith 45 Feb Jack Jones 80 Feb And so on,
What I need to do is see the overtime hours per month, and the production numbers for the same month. Something like...
Employee OT Production Month Jonh Smith 12 53 Jan Jack Jones 17 75 Jan John Smith 9 45 Feb Jack Jones 18 80 Feb
I have a pivot table the sums up the OT by PayEnd Date but need to change PayEndDate to month, which I know how to do, but then add in the production any thoughts on how I could do this?
How to streamline calculations that include multiple variables.
I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:
Associate Role Function 1 Count Function 1 Time Function 1 Learning Curve Function 2 Count Function 2 Time Function 2 Learning Curve Function 3 Count Function 3 Time Function 3 Learning Curve
[Code] .....
There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:
I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.
I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.
Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.
In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.
Is there any way to combine all the following calculations? Using the first as an example - If the word Resigned does not appear in P28 and if S28 = Q then the sum is 6 - 6*O28
having a different calculations performed within a Textbox on a Userform depending on the selection made in a Combo Box. This was answered here.
Perform Calculation In Textbox On Userform
I have another question regarding this problem, however the thread is closed so I have posted a new thread.
The original question was answered and the solution works very well,however I now need to be able to use the selection from two (2) comboboxes to initiate the various calculations in a similar vain.
have a look at the original thread to get an idea of what I am looking for here.
I have tried many configurations of various code, all of which works to an extent buts which falls over in certain circumstances.
I realised that the screenshot attachment makes much more sense than trying to show within post.
EXPENSE MONTHLY ANNUALLY MONTHLY COST ANNUAL COST
BILL £4.00 £4.00 £48.00 BILL £120.00 £10.00 £120.00 BILL £260.00 £21.67 £260.00 BILL £12.00 £12.00 £144.00 BILL £19.00 £19.00 £228.00 BILL £14.63 £14.63 £175.56 BILL £550.00 £45.83 £550.00 BILL £94.00 £94.00 £1,128.00
For my bills I want to work out first the monthly cost e.g. monthly(B) figure OR annual (C) /12 - and give the result in column (D) And then work out the annual cost e.g. monthly (B) *12 OR annual figure - and give the result in column (E)
So if there is no figure in the monthly column (B), the calculation will be dividing the annual column figure by 12 and vice versa, if there is no figure in the annual column (C) then the calculation will be multiplying the figure by 12
This is probably a very simple calculation to do, but I am struggling to make sense of calculating results from different columns to give a result in the same cell.
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 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
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.
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................
