Value For Variable Based Off Control Checkbox.
I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.
for an example:
if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5
I have a bit of code that is like this....
ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Assigning Macros To Control Toolbar Checkbox
I am an average user of excel and i am wanting some help with macros. I can record macros, that i do understand. Its the part where you can assign the macros to the Checkbox i have created. The effect i am trying to create is that when the checkbox has a tick in it, certain properties happen to a cell. But when the checkbox has no tick in it something else happens to the cell. I have recorded 2 macros for what i want to happen its just getting it associated with the true/false of the checkbox. I have no Visual Basic Experience and i have tried reading other posts, but the code just goes right over my head at the moment. Thanks in advance for anyhelp. I can attach the spreadsheet etc...
View Replies!
View Related
Print Worksheet With Checked CheckBox Control
I am creating a main worksheet (Legend) in which information will be entered and then populate to other areas (worksheets) within the workbook. I would like to create a print macro - that will run and print all the worksheets that we have selected on the "legend" page. These worksheets would have been selected by ticking a check box. My question is how to associate a check box to a worksheet? For example - the second worksheet is named " Schedule A" 3rd "Schedule B" and so on. - The check boxes will be name schedule A, Schedule B and so on. - I would like to associate (link) the checkbox Schedule A with the Worksheet of the same name. Then when this checkbox is selected, and the print macro runs it will print the worksheets that are selected.
View Replies!
View Related
Loop The Procedure With The Variable Number Included In The Checkbox Name
I'm creating a user form that will have 10 checkboxes on it. Depending upon certain conditions being met elsewhere in the workbook, I would like to populate the checkboxes' captions with data from the workbook. My question is, can I loop the procedure with the variable number included in the checkbox name (well, more to the point, HOW can I loop the procedure...)? I would like to do something like this: Dim a As Integer For a = 1 To 10 With Worksheets("Hi-Tech") If .Cells(a + 1, 2).Value "" Then chkHiTech & a.Enabled = True chkHiTech & a.Caption = .Cells(a+1, 2).Value lblHiTech & a.Enabled = True lblHiTech & a.Caption = .Cells(a+1, 2).Value etc.
View Replies!
View Related
Set Range Variable To RefEdit Control
I'm trying to set up a userform with a refEdit box that allows the user to select a cell range. I then need to assign that cell range to a range variable so the macro can use it. I'm having a problem reconciling the assigned string variable that refEdit returns and the range variable that I need. So what I'm trying to say is that I can assign the range fine through the Userform, and I can see through the debugging screen that mySampleVariable (dim as a variant) holds a range value of 'Access Dump'!$A$8 (the correct range), but when I try to Dim myRange As Range Set myRange = mySampleVariable The code bugs out because it apparently can't assign a string value to a range variable.
View Replies!
View Related
Set Variable To Forms Control & Get Value Property
How do I reference a spin button from a variable that I set. e.g Dim objSpn As Object Dim y As Integer Set objSpn = ActiveSheet.Shapes("spnWCDate") y = objSpn.Value This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it. ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?
View Replies!
View Related
Clear Row Based On Checkbox
I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared. If the checkbox on J4 is UNchecked, then that row will be skipped and so on.
View Replies!
View Related
Create New Workbook Based On Checkbox Selections
Could anyone please help me frame a vb code for the below explanation? I have a sheet where in some terms are provided. Users have to open this sheet and check its description. After going through all the terms, they have to select the required terms using a checkbox given beside these terms. After checking the reqd. boxes, they would click on 'Submit' at the end of the sheet. Once Submit is clicked, a new excel workbook should open up with the selected terms as various column headers.
View Replies!
View Related
IF Statement- Created Based Off A Checkbox Answer
I am trying to get an IF Statement created based off a checkbox answer. I have a cell reference C2 from Checkbox that is answer Y. If "Y" I want to calculate *0.005+0,0.03),0)*C4. I need to skip the 1st & 2nd quarter after C4 before the calcautaion starts. Then continuing no greater than .03 till the date changes. I have tried to rearange this formula every way I can think of to get it to work like I want it to. a correct formula for me to get this to calculate?
View Replies!
View Related
Automatically Calculate Based On CheckBox State
I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds: Private Sub Worksheet_Change(ByVal Target As Range) Dim intValue As Integer If Not Intersect(Target, Range("W12:X24")) Is Nothing Then intValue = CInt(Target.Value) If CheckBox1.Value Then intValue = intValue * -1 Select Case Target ' Apples Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + intValue Case Is = Range("X12")........................
View Replies!
View Related
Checkbox To Change Cell Value Based On Other Checkboxes
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.............................
View Replies!
View Related
Macro That Edits Formulas Based On Checkbox
When I click this button, a new row is inserted. Depending on whether this checkbox is checked, I want some of the formulas to adjust. Public Sub AddEntry() thisone = ActiveSheet. Name FinalRow = Range("A65536").End(xlUp).Row Range("A" & FinalRow + 1).Select Selection.EntireRow.Insert 'just inserted a new row x = Range("T1").Value Sheets("Calculator").Select Rows(x + 3).Select Selection.Copy Sheets(thisone).Select Rows(FinalRow + 1).Select...........................
View Replies!
View Related
Call Macro Based On CheckBox State
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen: Private Sub CommandButton1_Click () ' If CheckBox1 = marked Call MacroA 'Else (CheckBox1 = unmarked) Call MacroB ' End If End Sub
View Replies!
View Related
Copy Or Clear A Range Based On Checkbox
I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.
View Replies!
View Related
Automatically Create Named Range Based On CheckBox
I am quite new on this forum. I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P? I have multi checkbox in column F, So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1. I want to entry data in the Range H14:P? by this code Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
View Replies!
View Related
Return Value To Cell Based On CheckBox TRUE/FALSE
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook. Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
View Replies!
View Related
Tally Cell Automatically Based On CheckBox State
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("W12:X24")) Is Nothing Then Select Case Target Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value) Case Is = Range("X12") Logbook.Show Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value) Case Is = Range("W13") Logbook.Show.................
View Replies!
View Related
Option Button Properties Change Based On A Checkbox
I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem Private Sub cbpDiscAlum_Click() Dim myOption As Control Dim myValue As Boolean myValue = cbpDiscAlum.Value = True If myValue = True Then For Each myOption In pDiscounts.Controls myOption.Visble = True myOption.Enabled = True Next myOption Else For Each myOption In pDiscounts.Controls myOption.Visible = False myOption.Enabled = False Next myOption End If End Sub It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.
View Replies!
View Related
Filter Sheet Based On Checkbox & Listbox Selection
I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together.
View Replies!
View Related
Select A Variable Based On A Variable
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet. To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field. The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively. To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
View Replies!
View Related
Text Based Treeview Control
i found this quick online tutorial [url] on treeviews. i was trying to modify the code to make it a simple tree contaning just text like attached image, and to have a command buttton to add, and delete from the tree.
View Replies!
View Related
Vba Cell Control Based On The Cells Contents
Is it possible to place formulas in vba to control cells based on the cells contents. A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.# if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
View Replies!
View Related
Select Worksheet Based On ComboBox Control Choice
i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below. If cbomonth.Value = January Then Sheets("January").Select Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Drag From Treeview Control To Spreadsheet Control
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it. I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control. I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
View Replies!
View Related
Determine Active Control On Multipage Control
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View Replies!
View Related
Calculation Based On Variable
Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well
View Replies!
View Related
Variable Based On Inputbox
I have a macro that is designed to use an inputbox to ask for a number string that represents an entry on the current page, then go to that entry on the current page, and then select various info from that row and then use a portion of that string to move to another sheet which has the name equal to that portion of the string to perform more actions. My current problem is that when I try to set a variable based on a portion of the text input into the inputbox entry it gives me: runtime error 1004: method range of object _gloabal failed. And the line it errors on is the variable definition which is based on a portion of the inputbox entry. Dim MyInput As String Dim ChooseDate As String Dim Hours As String Dim SR As Integer Dim ER As Integer Dim SC As Integer Dim EC As Integer Dim RowVar As Integer Dim ColVar As Integer Dim found As Boolean MyInput = InputBox(" Date-Time Number?") If MyInput = vbNullString Then Exit Sub ChooseDate = (Mid(Range(MyInput), 1, 4)) The "choosedate=" line is where it errors. It's suppose to take, as a string, the first four characters of the text input into the inputbox. Then it searches the current sheet for the entire entry, selects it, uses that row to collect more info and then moves to the sheet in the workbook that has the name equal to the first four digits of the inputbox text to do more actions. I'm sure it's a syntax error but my knowledge is at it's limits.
View Replies!
View Related
Automatic Data Based On A Variable
I would like to enter a vacation code in E5 (V12) and have it automatically enter "VACATION DAY (12 HRS * $15/HR) in K5 and calculate the amount in L5. Of course, there are variables involved here: V indicates that it is a vacation day. 12 indicates the number of vacation hours to be taken. The $15/hr comes from the value in I5. I don't know if this should be handled as a VLOOKUP along with other methods or if this is something that needs to be handled in VB?
View Replies!
View Related
Different Calculations Based On Variable Inputs
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.
View Replies!
View Related
AutoFill Based On Variable Last Row
What I need to be able to achieve is a function in my macro that will a: Count the number of rows in the active sheet b: Allow me to use this info in other functions, such as subtotals, autofills After searching many sites I'm pretty certain the function I need to use is "rowcount". I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows RowCount = ActiveSheet.UsedRange.Rows.Count For x = 2 To RowCount Cells(x, 42).Select ActiveSheet.Paste Next x So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over Range("AQ2:AX2").Select Selection.AutoFill Destination:=Range("R2C43:RxC50")
View Replies!
View Related
Change Number Of Zero's Based On Variable
I need the variable here to be 7 numbers long, if not I need to add zero's at the beginning. However the problem I am having here is when then number of zero's is greater than one. Basically in the example below the variable 'Zeros' = 2 so I need the variable 'Variable' to give answer "00", how do I do this!? If Len(ActiveCell.Value) 7 Then Zeros = 7 - Len(ActiveCell.Value) Variable = CStr("0") * Zeros End If
View Replies!
View Related
Lookup Based On Variable/Changing Sheet Name
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across: The following is the way my spreadsheet's first page is set up: ID Apr-08 Mar-08 Feb-08 1 2 3 The column labels depend on another table, which changes monthly. The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this: ID $ amount Date paid 1 2 3 I have another table upon which the na I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table? This is essentially what my formula looks like right now for cell A2: =VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE) If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.
View Replies!
View Related
Counting Unique Values Based On A Variable
I'm having difficulty trying to write a formula for the following type of data: [data] .... This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...
View Replies!
View Related
How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet. here is what I have. Sub clear_data() sheets("sheet1").select lr = activesheet.usedrange.rows.count Here is where I get lost in translation (syntax). I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change. I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.
View Replies!
View Related
Copy And Insert Rows From List To Ws Based On A Variable No
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method: 1) Select and Copy entire row 2 from sheet(1) 2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable. 3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3. 4) loop through all records until complete I have tried writing the code in VBA but am not even getting close!
View Replies!
View Related
Countif Function For Counting Values Based On More Then 1 Variable
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done. For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall. You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.
View Replies!
View Related
|
TRACKER
