I need to make an excel sheet with checkboxes (around 50) which each add a value to 1 cell when clicked.
If you click them again the value should be removed again from the cell.
So lets say I got cell A2 and I got checkbox 1 to 5. Checkbox 1 has value 1 checkbox 2 value 2 etc. up to 5.
So if I click Checkbox 1 and 4, cell A2 should display "5". If I check Checkbox 1 off it should display "4". Now I tried to do this but I really cannot get cell A2 to display "4" again (clicking Checkboxes again just adds another value).
I know why this is the case, I defined checking the checkbox as click, not as "check" but I have no idea how to do this correctly.
Little info:
I have little to no experience in VB but got some programming experience next to it. I am using Excel 2007 (forced to, work).
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1 123-45678-A-10 123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A Col B 123-45678- 1 123-45678- 10 123-45678- 2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
I would like to be able to add a checkbox linked to each sum cell that adds the values of each of those cells.
Ex: A5=4, B5=6, C5=1, D5=7. With a checkbox linked to each cell, if i check the boxes in A5 and C5 then in another cell i would get the sum of those two cells= 5.
I've tried this but every time i link a checkbox to the sum cell it deletes the formula and puts in a True or False result.
- 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 trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E" Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I have a list of line entries for which I need to insert a variable amount of empty lines per line. (the variable amounts listed in a column to the right of my Active Row.
Unfortunatly I loose the ActiveCell's Value. During Debug it shows the correct cell and value, but after the Debug Step Over, the value shows 0 again. I include my [code]
Sub IP_Insert_rows() ' ' IP_Insert_rows Macro ' Macro recorded 2006/05/29 by Joseph Clark ' Dim GrpCtrVar As Integer Dim LnCtrVar As Integer
To best explain my problem I will use this example
Dog 2 400 Cat 4 300 Car 1 200 Dog 7 800 Car 8 900
That data is in the top of my worksheet, and I would like to be able to find all the dog values then reference the 2nd and 3rd column and add all the dog values up and then post the totals in a area below like this
dog 9 1200 cat 4 300 car 9 1100
Stipulations 1) there are different amounts of values, there may be 1 dog value or 10 dog values, I would like the program to recognize the dog values and add the corresponding values. 2) dog, cat, car values are not the only values, there may be others like "tree, apple" but are not in use. So only values in the top of the spreadsheet get referenced to the bottom part.........................
In the attached file (I'm sorry it is zipped, it was just too big), I gave you a snippet of my database. The actual one has 9000+ records. Over in Y4, you'll see the Sum of all the amounts (column X). Run the first macro, that is, Copy Dups, and you'll see it splits the item up by the numbers in column N. This works perfectly (or so it appears.) Then, run the Summary macro (the only other macro in there) and you will see that it pulls the Sums of each spreadsheet and places them on one nice overview page. The summary page. The only problem is, the total of the subtotals on the summary page do not match the total on the main (unadultered) page. I hope i am explaining this right. Somewhere in the macros, I am losing data from my spreadsheets so that the summary page is not equal to the un-macro'd data. /scratches head
As you can see this macro copies a worksheet from one workbook to a new one. It is 14 columns wide by just under 700 rows long. I am losing information out of cells all over the place.
In cell L7 I have (80/60)/24 and formatted h:mm to get 1:20. In cell M11 I have 1:00 (h:mm). In cell M12 I have L7-M11 and get the result 0:19. Why I'm losing a minute and not getting the result 0:20?
I've got a function called countcolor (which I take no credit for) which counts a particular color in a range. --------------------------------------------------------------------- Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range Dim iCol As Integer Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = vResult + 1 End If Next rCell
CountColor = vResult End Function
I have then set up a couple of macros which, when ran, fills a range of cells with a particular color. These colors are closely related but as you can see from the two macros below they ARE different. I'm finding my countcolor function is counting them twice though even though as you can see the color values are different..... --------------------------------------------------------------------------- Sub pegcharged() With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 25600...........................
I have an application where by the user only sees userforms which then writes the data to the back end worksheets. So the Menu or front useform I have set at vbModeless, which also means that the user can now open up or use an already opened spreadsheet. I believe I am correct in saying you can't do this if the userform is modal. Anyway this is what the user wants, i.e to be able work on multiple spreadsheets, whilst my app is open. The problem is when they click on one of the other open spreadsheets and then click one of the buttons on my application, you get an error.
I believe is because I use code such as
frmMetrics.Show
Where I haven't specified the actual workbook, so excel doesn't know where to go to find the userform. So how do I specify the workbook, without constantly having to change the line, when version numbers changes ?
eg
Application.workbooks("Book1") For the next revision the file may be called Book1.1 Application.workbooks("Book1.1")
This is probably a simple one but I can't figure it out. I am building a workbook from scratch. It has about twelve of thirteen tabs at the bottom. On one of the tabs I would like to copy and paste a stand alone worksheet. The worksheet functions properly when it is stand alone. However when I copy and paste the worksheet into this new workbook none of the formulas transfer with it. I do get the cells filled in, but I would like to get the formulas.
I have done this before and it worked fine. In fact I can open a new workbook and copy / paste the worksheet into it and everything works fine. Again when I copy / paste it to this new workbook the formulas do not transfer.
I have a range of data (techniques) - example below....
AQ - Ask what other topics to include No DT - hjhjh ISL - Ask how Saj would manage the time AQ - Ask if there's anything else Saj wants to cover
I need to be able to calculate how many different techniques are used. No DT shouldn't be included in the count and if a technique is repeated, it should only be counted once. So the answer to the above is 2 as AQ appears twice and there is a No DT.
I have an Excel 07 spreadsheet containing multiple tabs, modules, and userforms. In Module1, I have a public variable declared as boolean - will call it X. When X is selected from a combo box in Userform 1, X is set to TRUE. However I've noticed that when the user enters Userform 2, X is somehow set to FALSE.
I can't set it back to TRUE at that point because more often than not, it SHOULD be FALSE, thereby sending the macro down a different path. Any idea how I can retain the "TRUE" value for X. I've tried changing the Public Variables to Global, but am still having the problem.
I am trying a macro to merge cell without losing the data until I find a next cell with the data - This merging should be pertain only for the column selected. Is it possible to write a macro and preform this activity.
Is there a way to prevent formulae from losing their references when the references get replaced.
I have formula which refer to a worksheet that is dynamic, meaning that the worksheet holding the data (CONTROL_1) differs from one query to the next is imported from another source. The datasource contents and format are identical.
I have the same problem as Paul C in the following post:
(Pictures disappearing) "Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i re-open it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear.
I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried re-installing Office 2003 and installing all the latest updates but still get the same problem, any ideas?"
I have tried:
- Tools > Options > View > Objects > Show All - Changing macro security level - disabling macro security in my antivirus
I'm using a VBA UserForm (ShowModal=False) as the front-end on a spreadsheet for logging purposes. Here's the progression of the problem:
1. I have the UserForm loaded and the cursor is in any given textbox/ combobox on the form.
2. I switch to another program, then come back to the UserForm.
3. The cursor no longer appears in whatever textbox/combobox I was in when I left the UserForm. If I type, nothing happens (I have to click the field again first, then type).
However, if I TAB, it will go to the next field in the Tab Order just fine. It seems that the control has "pseudo-focus"--it knows which field to tab to next, but the control won't accept input unless you click it. The odd thing is--this UserForm has a button which launches another "child" UserForm. That "child" UserForm does not have this problem.
I am trying to create a spread sheet where I have different columns. The columns consist of name and dollar amount. Names may occur more than once. I would like to scan the column with the names and copy the different names to another column. Once that is done, I am hoping to copy the value or sum of all values associated with that name to the column beside it. I am going to have a long list of names with different values and I would like to sort them and add up those values. I have attached an example of what I am hoping to achieve. I am hoping my 'Sum of Values' is actually summed, I left it longhand for explanation sake.
This is a simple one, for someone who is smarter than I. I need to add a column but omit the rows that are blank or has a zero value in either row C or D. In other words I want to add every row in column C if there is a value in row C&D of that item. Attached is a small example.
Using the attached example, how do I get Excel to display in cell F8 the correct SUM of cells F4 & F5 without having to manually enter the SUM in column F. I need F8 to the be the SUM of the amounts that are displayed.
