I often use the .TAG property on controls as a sort of temporary container to hold variables, arrays and such but this gets messy because I sometimes forget which control I used for what since there appears to be only 1 tag property per control. Are there any controls that have numerous .TAG like properties I could use (even name) to hold my temp data?
I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.
I need to insert a drop down box into my spreadsheet, and depending on the option selected, the formula used to calculate the value into another cell changes. How do I implement this in Excel 2007?
To put this in context, I need a list of acids in cell C3. Depending on the acid selected, the formula used to calculate acid concentration in another cell (D3) changes. I need a way to implement this.
I am using Excel 2007. I would like to be able to type "dlta" followed by a space and have autocorrect change it to the symbol for Delta (a triangle). I got it to work in Word because I could right click and paste the symbol in the autocorrect dialogue box. Excel won't let me do that. I also tried typing Alt+68 because I believe that is the code for the symbol I want but all I get is a "D".
with the following: The VBA macro I am using, is derived from the excellent "Table of Contents" macro written by Erik Van Geit, a few years ago.
I have a workbook (in Excel 2007, Windows XP) with an Index sheet and 200 other sheets (called ScreenShot1 to ScreenShot200). Each of these 200 sheets has had an image pasted in the top left corner.
At present, using the macro below, a hyperlink is placed in cell A1 on each of these 200 sheets (it's behind the image) that links to cell M1 on the Index sheet. What I would like this macro to do is make each image the hyperlink back to cell M1 on the Index sheet. Please note that the images are not named.
VBA code below,
Sub create_TOC() Dim i As Integer Dim msg As String Dim fc_order As Range Dim sht As Object '**** EDIT the following lines **** Const TOC = "Table of Contents" Const Index = "Index" Const CellLink = "A1" '**** END EDIT ****
I have a form that collects four pieces of information from the user and places each into columns A, B, C, D on a sheet. I would like to automatically add an option button to column E so that a user could select it and then click a control button to delete the corresponding row.
Is this possible or is there maybe a better way to do this in Excel 2007.
I am currently using MS Excel 2007. Is there a way in VBA to automatically insert missing columns based on a defined series or range of values?
For example, we have a report that displays columns of: Year 2004 to Year 2012 per column (9 columns in total). Sometimes, it displays missing a year or years (thus; our report has less columns). For example, it only shows 2008 to 2012 (missing 2004 to 2007). At times, it misses some years in between. What we do is manually add those missing years by manually inserting columns then inputting the missing year as their column headers.
Is it posisble for VBA to check if the columns contain all of the defined Year range (for example, you've initially defined: Year 2004 to Year 2012)? Afterwards, all missing columns are automoatically inserted (in chronological order or based on your defined values or range).
Using excel 2007 I've created a new tab and wanted to place the 2003 file menu on it. I don't know how to include it automatically in the tab using a combination of XML and VBA. So I created a button with the callback to run some code. Unfortunately, it still places the item in the Add-in tab. How can I get the undernoted code to operate within my new tab with either XLM or VBA?
Code: Sub InsertFileMenu2003() Dim cb As CommandBar Set cb = Application.CommandBars.Add("xl2003 Menu", , True) CommandBars("Built-in Menus").Controls("&File").Copy cb Application.CommandBars("xl2003 Menu").Visible = True End Sub
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
I am trying to write a Macro that will insert a Text Box that auto-fits the shape of a cell to hide its content. Once finished, the Macro will need to lock the cell and the text box so the contents of the cell are hidden. The idea is that I want to share a spreadsheet with someone but want to hide individual cells for various reason.
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
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 have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
" JOES TOWING SERVICE  123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC  21234 HYDRAULIC DR
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
I am using Excel 2007 and am attempting to write multiple IF statements within a cell. This multi-cell validation would be used to validate that A=B and C=D but A&B do not equal C&D as illustrated below:
I have Excel 2007, and I have column 1 with a thousand entries "HC 14-0001" all the way down to "HC 14-1000". I have on my hard drive 1000 foldiers named the exact same "HC 14-0001 to HC 14-1000". I want to create a Hyperlink from each item in that column to its corrisponding folder on the hard drive. I can easily create the Hyperlink, but I want to find someway to copy them. I can create the first two rows... HC 14-0001 and HC 14-0002 and create good Hyperlinks to the folders, but when I highlight them and drag them down, the excel spreedsheet is updated all the way to HC 14-1000, but the Hyperlinks do not change.
How can I get the Hyperlinks to change? I don't want to spend the next week creating Hyperlinks for a 1000 entries on this spread sheet, and I have 5 other spreadsheets exactly the same.
I'm looking to simply find a function that allows me to lookup a certain value in a table of data and return the value in a specific column. So if I looked up the current date, 6/27/2011, it would return the week it is in (26). I dont want to be limited to just the monday column however, as the dates I will be throughout the entire week. Is there an easy way to do this in excel 2007?
I am using Windows xp and Excel 2007. I'm creating a user form and have a vlookup formula that goes to a table and looks up calories and sodium based on the text entered by the user. My problem is that I want to be able to populate the 3 columns with information and then sum the values that are returned to the Calories and Sodium columns. Is that possible with Vlookup?
I am having cell return blank if no value is found. it is currently looking only at one sheet.
I don't want it to combine the values like this formula does. =(VLOOKUP(Q2,'sheet3'!$D$2:$K$200,7,FALSE)&","&VLOOKUP(Q24,'sheet2'!$D$2:$K$1807,7,FALSE)) #.###,##.##
sheet 1 is where the cell I am writing the formula is in, and I want to look in sheet 2, 3, and possible 4 for a value, I want to only have the greatest value show between the sheets. is this possible.
I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)
Dim Rng As Range Dim x As Long Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row) For x = Rng.Rows.Count To 2 Step -1 If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down End If Next x
After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.
Here is an example of the data I have before the Macro with Columns.
Column B Column C Column F Column I
Destination of order booking ref Size weight of orders
This is how I would like the date to look like after the macro.
Column B Column C Column F Column I
Count Destination of order booking ref Size weight of orders