I would like to add a menu option that will run a subroutine to automatically add a formated text box to the worksheet at the selected cell location. The box must have the text centered, bold, underlined in Arial 10, and no border. I use this method to create a flow chart and would like to make it easier. At the moment I create three different text boxes and then copy them to the clipboard. Then I just click each one, drag it into position and edit the text. I tried recording a macro while adding the text box to the worksheet, but it didn't record anything except the cell selection.
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
I am stuck because I am inserting a row in a spot that is sometimes below the Labels row, and sometimes its not. So i want it to insert the row, but i want it to take the formatting from the row below it. this is my current insert row code line:
VB : Cells(RowNum2, 1).Offset(1).EntireRow.Insert
How do I alter this so that it copy the format from the row below it instead of the above row which seems to be the default?
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I am using the code below to insert and resize images in excel. I was wondering is it possible to set up this macro so that I can just provide the directory and then have it so that just a random image is inserted rather than specifying an jpg number. The reason i need this is because the numbers in the folder range from 00000 to upto 20000 however not every folder has the full range of images i.e. Z:SingaporeSingapore TurfSingapore Turf Club 2QEII Cup 2006QEII Cup 2006_VDMillenium Copthorne InternationalTVGI Race Name Text may just contain one image and that would be image number 03965.
The other thing I would like to do is to have the name of the las 2 subfolders inserted into the cell above the image so for the example below you would have:
Millenium Copthorne InternationalTVGI Race Name Text Picture
Millenium Copthorne InternationalTVGI Race Name Logo Picture
I wanted to make a simple macro forinserting a comment, given certain parameters: margins & alignment...but its not working...how would i get this to function properly
In the sample i have list of codes,also i have picture according this codes: Can i somehow conected pictures and codes? My pictures are in jpg form,can i insert them into cell,where picture should has form as cell has.
When the code from the Data sheet in cell A2 sheet 1,than show the picture conected with this code in Data sheet.
I've got a table in a worksheet. Every time the user double-clicks on one of the cells containing the names for each of the listed items, a userform pops up displaying information about that item. Besides other controls, this userform has an Image Box, that displays a picture of the item, and a Multipage.
What I want to do is to display the same picture shown by the Image Box, on to the Multipage, using the same file, but, and here's the quirk, aditionally, change it's size, turn it monochrome, and set its transparency to a different value.
I've attached an example of the final look I'm seeking. This was done by prior editing of a copy of the image and inserting it using LoadPicture, but this solution is too troublesome as this is done solely for aesthetical purposes, thus not justifying all the hassle a user has to go through everytime he wishes to add a new item to the table.
I need to paste the format and formulas of the last row of data into a new row beneath it. I've seen a bunch of different end row functions, but which one is best and how do I only paste the formulas and formatting?
In D2 I have: =B2 In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.
Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.
I've almost gotten my routine finsihed but this is last remaining obstacle.
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?
what I am looking to do is when in colum AA a cell changes to "Drawdown" I'd like the cells in column A:D,F,H:L,N:Q,S:Z on the same row to grey out and a line to be insert below the cell that was changed, if it selects "Fee" , cells in A:D,F,H:L,N:Q,S:Z on the same row.
I have writen the following butit seems rather cumbersome and I can not get the insert line to work.
Code: Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim IntersectRange As Range Set WatchRange = Range("AA1:AA500") Set IntersectRange = Intersect(Target, WatchRange)
Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.
I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.
I have a few tasks which I have been doing manually because I do not know if there is a way to accomplish these tasks automatically via some excel formulas.
My first task requires me to take an address, for example: 1234 CAMBIE STREET, and format it such that it is properly capitalized (only capital letters in the front of words) like this: 1234 Cambie Street
My second task involves taking an address, for example: 1234 1st Avenue W, and rearranging it such that the direction is in front of the street name like so: 1234 W 1st Avenue. What makes this task potentially even more complicated is that not all the addresses I am working with require the rearrangement, so I can't simply have a formula that puts the last group of characters in front of the first group of characters because that may screw up addresses that don't require this formatting.
My third task demands that 2 columns of names are combined into one column, while also simultaneously placing a "&" between the names. For example: Jonathan Parkinson | Sarah Parkinson turns into Jonathan Parkinson & Sarah Parkinson. Now I do understand how to use a simple combining formula (=a1&" & "&b1) but it isn't that simple. The columns are not all filled with names. Some pairs may have no names, whereas others may have only one name. Because of this, the formula I used as an example will result in many instances where all I see is a "&", and other instances where I see the first name, followed by an unnecessary "&".
So far, for the majority of these problems, I've been using a combination of manual data input, and the replace function, but I really do hope there is a faster and better way to go about these tasks.
I've create an userform with 2 textbox and a command button.
The user is allow to paste an article into textbox1, when they clicked the button, it should be able to find a specific text string in the article, then right after that text I want to insert addition note and a new article with notes will be generated in textbox2.
Currently i stuck on how to insert the note after the specific text string?
Got a slight problem; I am making a "worker-plan", where you should be able to write a name on one file and have it updated (removed) from the equivalent columns on another file automatically. Here's an example with two separate projects:
Project 1 before and after changes: Attachment 257366 Attachment 257367
Project 2 before and after changes: Attachment 257368 Attachment 257369
You can see here how I intended it to work. When one worker is moved from Project 1 to Project 2, that worker should also be removed from those weeks in Project 1. The previous worker from Project 2 should also be removed from those weeks (in Project 2).
Are there any formulas that can make this work automatically? All week numbers will belong to the same columns in all files.
Edit: There are 30 files that should be syncronized, not just two.. I could also gather all files into one file with sheets, instead of having seperate files.
All I want to do is insert a formula into a text box. This thread answers the exact same question but I don't understand how to highlight the text box as an object... [URL]....
I need to enter text in a cell, then click on a command button that will run a macro to find that text in a column and if found, insert a row and copy down the formulas from the row above.
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.
I have the following code that allows a user to type in an email address into an inputbox, and email a spreadsheet to the recipient in the input box. However, as 99% of the time this is going to be the same email address every time, can I populate the inputbox automatically with a given email address? for eg email@email.com
vaRecipients = Application.InputBox("Please enter recipient's email address. Please ensure Lotus Notes is open before sending.", "Email Literature Request")
