I would like to implement a pricing tool where by if you select certain boxes the price will be increased or decreased.
For example. If chk_UtilA is selected on the attached then the target price will be multiplied by 10%.
My worksheet contains the following:
- Column G includes fifteen rows of a formula (beginning at G9) that returns a dynamic list of values based on criteria chosen through a dropdown box. The list can be anywhere from five to 20 values depending on the selection. When the potential matching values for the list are exhausted, the formula returns ""
- Column F includes one checkbox for each of the 20 rows containing the formula.
I need a script that will hide each checkbox that is next to a "blank" value. That is, if the cell in Column G = "", I need the corresponding checkbox to be invisible. Unfortunately, hiding the entire row is not an option. If the script could uncheck the boxes when hidden, that would be a bonus, but isn't absolutely necessary
I am making a form. At the top there is a dropbox with a list of applications. Based on which application the user picks, I want another section of the form to populate check boxes with the name of the projects that are associated with that application. Some applications have 2 projects, some applications have 7 projects.
View 12 Replies View RelatedEvery thread I see on Google search for creating check boxes on a user form based on worksheet data says to use a list box, but I really want check boxes. Any code that will create, on initialize of the user form, a check box for each item in Import!B2:B (last row), regardless of the number of items? I am only worried about having about 20 max. Usually, it will only be 5 to 10.
I did try the list box, but it doesn't populate the list box.
Code:
Private Sub UserForm2_Initialize()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
'Set reference to the range of data to be filled
[Code] ..........
I currently have a spreadsheet with a userform that has multiple checkboxes. I want these checkboxes to change the value of cells depending on whether or not they are checked. 1 for not checked, 2 for checked. The column of the named range the cells are in is constant for each checkbox (though it is different for each checkbox and some checkboxes will need to change the value of a cell in multiple columns). The row changes depending on the value of a combobox on the userform. I have sorted out a code that works, but it seems very long, slow, and inefficient. I'm hopping someone can show me a better way to do it.
Private Sub CmbFinish_Click()
Dim rClSkills As Range
Dim rClLookup As Range
Dim var1 As Integer
Dim iDecision As Integer
Set rClSkills = Range("TblClSkills")
Set rClLookup = Range("ClassLookup")
iDecision = MsgBox("Are you sure you wish to change Skills?", vbYesNo, "Continue?")
If iDecision = vbYes Then
var1 = Application.WorksheetFunction.Match(cboClass.Value, rClLookup, 0)
If CboAppraise.Value = True Then.............................
I have a sheet with around 3,000 rows of data. I need to be able to enable/disable different comibinations of three checkboxes based on the value of a cell in column D as follows:
If Band says £0k - £75k then all three check boxes should be disabled
If Band says £75k - £250k then the Step 1 check box should be enabled but the Step 2 and Step 3 check boxes should be disabled
If Band says £250k - £500k then the Step 1 and Step 2 check boxes should be enabled and the Step 3 check box should be disabled
If Band says £500k plus then all three check boxes should be enabled
When a checkbox is disabled I'd like it to appear visible but greyed out so it is obvious the option is not available, maybe via conditional formatting. I don't know if this can be achieved within any code rather than having to manually add conditional formatting afterwards?
I've attached a sample to demonstrate. Is this possible to achieve? I'm open to alternative approaches as well if this makes things easier to do. The mere idea of adding 9,000 plus check boxes if already scary!
How to create checked and unchecked checkboxes based on three criterion(in sheet 1)?for clear explanation..it will be like this...firstly,the "SELECT TAKEN only"checkbox will checked/unchecked row d20:r20.secondly,the "SELECT RETURN only" checkbox will checked/unchecked row d21:r21 and so on.lastly,"SELECT ALL" will checked/unchecked all the rows.
Checkbox1.xlsm
I have a list in Excel with several hundred 8 digit ID numbers in column D. Column C contains the total number of hours the student employee worked. Column E needs to calculate using two different equations depending on if the ID number in column D is a manager's. If the ID number is an employee the total hours worked is divided by 8. IF the ID number represents a manager the total hours worked is divided by 4. I've attempted this by using an array for the manager ID list and also using VLookup without success.
In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.
I'm trying to do two things:
1. I'm trying to show the formula of specific cells in the work sheet. So far I've only found out how to show formulas on the entire worksheet. I imagine there has to be some way of me doing this without using macros.
2. Here's the doozy. What I'm trying to do is use the base code for a formula that is stored in one cell (Formula Page: B5:B7) and apply it to other cells (Calc Page: C4:C6). In other words, I want to apply the cell formula from B5:B7 to the cells C4:C6 and use them to calculate the TOTAL COST. I want it so that whenever I manipulate cells B5:B7, they are also appropriately manipulated in C4:C6.
Please see attached for reference. Attachment 33077
If I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is: eg: If Column B = EURUSD pairs and the like: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*10000.
This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3. (eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)
When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000). This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....
So if Column B = USDJPY the formula would need to be: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*100 . Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation. Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.
i've been viewing different formuals, and have to admit there is alot more to excel than I originally thought, and totally stumped as to what and how to go about my problem.
I have a problem, and it mainly stems from my employer not paying me correctly (and pretty much the other 1600 odd employees encounter the same issue too on a semi-regular basis).
To give you a run down, this is our complexed pay system
Mon-Fri, if you work up to 7h36m you get 100%, then 150% until 10h36 and then 200% after 10h36m working time
Sat, if you work, its 150% upto 7h36m and then 200% there after
Sun, double time all shift
But to throw in some complicated things, here are some more items that need to be considered.
If you work from one day into the next (say fri into sat, and sat into sun and sun into mon, then after midnight, it then goes to that days rates)
If you work your day off, say a weekday, your then paid the eqivelant of saturdays rates unless you were called in straight away which then is sundays rates
If you work your day off, say its a saturday or sunday, then its 200% for the whole shift.
And then public holidays, its 250% upto 7h36 and 300% thereafter
So as you can see, its pretty complicated, and my employer (a government employer) can't seem to get the right software to pay us correctly, nor can the poor employee sometimes work out if they've been paid correctly or not.
So, this is what I have so far:
columns: Day, Type (normal day or work day off or work day off immediate), On1, Sign Off1, Sign On2, Sign Off2, Sign On3, Sign Off3, Working Time, Paid hours
(for instance its my normal rostered monday, I start at 12pm, lunch at 5, return at 545 then finish at 915pm, which equates to 8h30m working time and 8h57m paid time) - the 3rd sign on and sign off is if we do extra work beyond our shift.
There are other things aswell like allowances, but thats for another day, and not as urgent as what i'm really looking for.
Now if anyone can understand what i've just written above, your doing pretty good (even some of the most experience employees still don't understan our pay system), and anyone able to produce a formula for what i'm looking for, i'd be exceptionally grateful, and probably the 1600 odd other employees too who seem to get shafted occassionally.
I am just starting to use the wonderful pivot tables.
I have a long list of data which basically consists of the following info:
-Company Code
-Country
-Account description
-Account balances
The pivot table is set up like so:
Row labels - Company Code, then Country
Column Labels - Account description, eg Input Tax, Output tax, clearing account, import tax, acquisition tax, set off account
Values - Account Balance
The table works perfectly.
Now what I want to do is some calculations based on the fields
- a check that Acquisition tax = the negative of the set off account, (i.e net to nil)
- a sum of the balances in the import, input and output tax accounts
- a comparison of that last figure against that in the clearing account to identify a difference
I tried to do it using basic formulae but I can't drag and drop the formula down the rows.
And I couldn't make out what the calculated cell function does. It appears to only include the titles of my data and not the individual fields.
I am having problems with calculations to be performed on date format. As in attached excel sheet, Start Date and End Date can be defined by the user. Once the user enters the dates, year 0 onwards are to be populated with values so that: (Also, not sure if I will need a button to initiate the calculations or a direct function will suffice)
year0 start = Start Date,
year0 end = (Start Date +12 months) or (End Date), if End Date is before (StartDate + 12)
year1 start = year0 end,
year1 end = (year0 end + 12 months) or (End Date), if End Date is before (year0 end +12)
year2 start = year1 end,
year2 end = (year1 end + 12 months) or (End Date), if End Date is before (year1 end +12)
year3 start = year2 end,
year3 end = (year2 end + 12 months) or (End Date), if End Date is before (year2 end +12)
year4 start = year3 end,
year4 end = (year3 end + 12 months) or (End Date), if End Date is before (year3 end +12)
year5 start = year4 end,
year5 end = End Date
If the dates are such that all years 0 to year 5 may not be covered, the years not used should be blank.
I am trying to display result of risk assessment matrix in excel. I have three columns PROBABILITY , IMPACT and RISK RATING (probability *impact).
Probability = Very Low(1), Low(2), Medium (3), High (4), Very High (5)
Impact = Low(1), Medium(2), High(3)
Risk Rating = Low (value between 1 -3), Medium (value between 4 - 6), High (value between 7 - 15)
Is there any way that I just use TEXT in the cell (visible to user) and excel does its magic in the background using the numbers that I have for each TEXT? I don't want to display numbers.
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
Indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations?
Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?
Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:
Code:
Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")
[Code] ......
I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:
Code:
.Range("K1") = .Range("sheet_name2.Value!A14").Value
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
I have a spreadsheet (Sheet A) with various data and checkboxes that I want to populate based on data in another spreadsheet (Sheet B). Sheet B has various fields with yes/no answers in the cells. I need to take this data and check varius check boxes in Sheet A based on the answers. I think this is possible if I write a macro to sweep through Sheet A to populate everything, but I was wondering if there is an easier way?
I would think there would be a way to set the initial state of a checkbox based on the value of a cell, but I can't seem to find a way to do it.
What I have is a long list of about 100 items. I would like to set up an excel sheet that allows me to place checkboxes in each row and then whatever checkboxes have been checked, to simply collate and print out ONLY the check boxes that have been checked.
I have gotten as far as to create and rename the checkboxes but my skill ends there. I also need to know how to get each checkbox to correspond to a row.
I appreciate ANY help that you can give me.
EDIT: this doesnt NEED to be checkboxes, it can be optionbuttons if that is easier... simply something to select/deselect and print.
1.1 a blah
1.1 b blah
2.1 a blah
2.1b blah
3.1a blah
3.1b blah
3.3.a blah
etc blah
etc
I have inserted checkboxes in an excel worksheet to turn on/off certain data. They are working just fine. But I have set them all to not print (by unselecting the checkbox that says "print" in the format dialogue). SOME of the checkboxes don't print, but most of them do. I can't figure out why.
I've attached the file so you can take a look.
how to handle checkboxes in VBA
I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1
what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.
what do I need to declare or set or ? to access the state of the checkbox
I have a userform that contains 15 labels, each label has Numbers in it each time i click on a label the text is inserted into an excel spreadsheet. I use it as a fast input solution and works well and has done for a few years.
I Now want to Add three checkboxes to the userform
If checkbox One is ticked i want the letter "A" and the number in the label i choose to be inserted into the spreadsheet.
Checkbox Two "B"
Checkbox Three "c"
I would only ever tick one of the checkboxes never two or three so it would be A, B or C
This is the code for The Labels i use
If I select checkbox1 I want checkbox3 to be false and vice versa, thats simple enough. Everytime I click on one or the other checkboxes I have to reselect the one I want.
It takes two clicks to get a checkbox checked (True).
My listbox will be populated by both lists in the "Checkboxes" code once I select a checkbox, then reselct to repopulate.
Checkboxes:
I have data on various KPI's achieved by diff states in different months.
I am looking an automatic way of loading the data upon selection of choices from the check boxes in the attached file.
Choice could be multiple from two different options available (Months & States).
If I select state 3 also in this, with months being same, state 3 data should automatically get appended in the data visible now.
I have a worksheet that has multiple (form type) checkboxes on it, and I want to write code to loop through a specific range of them (Checkbox 26 to Checkbox 36 for instance), check if the value is true and then return the .Caption value to a message box, or ultimately a cell on another worksheet.
I can do it for all of the checkboxes on the ActiveSheet, but just can't figure out how to run through.
I've got several rows of checkboxes, I want to be able to select 1 checkbox on a row and the others not to be selected, or if they are it automatically deselects them, and then I want to select 1 checkbox on the next row and so on.
View 9 Replies View RelatedI don't understand why this code doesn't work:
Sub All()
Dim chk As CheckBox
For Each chk In ActiveSheet.CheckBoxes
CheckBox.Value = Checked
Next
End Sub
All I want to do is loop through all the check boxes on a sheet and check them. But the code acts as if there are no check boxes on the active sheet, I don't get any error messages it just never lands on the
"CheckBox.Value = Checked" line.
I'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.
a. I m trying to put up a userform that a. select different autofilteroptions (I'm using checkboxes)
b. let the user to do som autofiltering of several workbooks (the workbooks are similar regarding structure and formats).
c. take a copy of the result from the autofilter result
d. paste this in a new workbook
e. go to next workbook, do b. and c. and paste this under the result from the last session.