Changing Selected List Option To Single Character?

Jul 7, 2009

I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.

In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example,
Column AK has drop down list with following options:


When a user selects one of the options, I want one the following respective letter to appear instead......

View 2 Replies


Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)


When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Assigning A Value From The Option Selected From The Drop Box.

Jan 26, 2010

I have a spread sheet where i have created a drop box in Cell i4. There are 56 choices in the drop box. These choices come from cells B160:B215.

There are dollar values in cells C160:C215 correspond with B160:B215. I am trying to find a formula for G16 that will look at i4 and find the corresponding dollar value from C160:C215.

View 3 Replies View Related

Using VBA To Change A Picture When An On Option Is Selected

May 29, 2006

I have a group box with 5 little round option buttons in, what i wanna do is connect the options to an image and when an option is selected the image that corresponds to it shows, then when a different option is picked another image relating to that option shows.

Select option 1, image 1 is shown

select option 2, image 2 is shown

View 2 Replies View Related

How To Insert Value Into Cell If Option Button Selected

Mar 9, 2012

What I am trying to accomplish is the ability to insert a value into a cell if an option button is selected. If the option button is not selected I want there to be a different value in the cell. This is what I have so far:

Sub OptionButton2_Click() Range("F8").Value = "$299" End Sub

What I am having issues with is the "else" clause. I want the value to show $0 if the option button is not selected. I attached a screen shot for reference.

View 1 Replies View Related

When A Drop Down Option Is Selected, Can It Show A Preset Value In Another Cell

Jan 14, 2009

If I have a dropdown with

A (value 1)
B (value 2)
C (value 3)
D (value 4)

and a person selects one of those letters in the dropdown in column 1, I would like column 2 to show the number value associated with it

View 14 Replies View Related

Display Range Based On Option Selected In Dropdown Box

Jul 12, 2013

I attached a sample spreadsheet but I was wondering if there was a code that could display a range based on an option selected in a dropdown box.

excel help.xlsm‎

View 3 Replies View Related

Mouse Focused Onto Textbox When Option Button Selected?

Mar 9, 2014

I was trying to have the the mouse focused on to the textbox when an option button is selected, but it seems not working.


View 2 Replies View Related

Running Code Based On Option Selected In A Combobox

Mar 26, 2007

I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question.

View 2 Replies View Related

Macro To Add Character To Selected Cell?

Jun 3, 2014

I am trying to create a button that when clicked it will add a character to the existing cell. Eg if cell had LDs and I click button I like it show LDs₂

The ₂ is character code 2082.

View 5 Replies View Related

Replace Just Single Character From Many Of Same One Within Text?

Apr 13, 2013

have to deal with a text database into Excel but ended up finding irregular format that need to be fixed. As it can be seen within the text right below, there are space delimiters in between the words. I want to replace always the 2nd space from right to left with a semicolon.

20/10/2012 CENTAURO CE 39 (06/10) 57.97 0.00
20/10/2012 CENTAURO CEFT 534 (09/10) 1,235.34 0.56
20/10/2012 CENTAURO (06/10) 5,345,200.00 45.00 1.01
20/10/2012 TFRE (06/10) 1.00 0.00

View 5 Replies View Related

Add Single Character Before & After Each Cell Value In Column

Jan 27, 2009

I am trying to do is put an asterisk at the beginning and the end of the Information in a cell for the entire column. Is there a way to make a macro that, once I highlight a column, it will put an asterisk at the beginning and end of the information in each cell in that column until a blank cell is reached where the macro will then stop? This will save me a lot of work. It will work for me if a particular column has to be defined in the macro, but would be better if it is one I can just highlight.

This would also work fine for me if I just had to highlight the cells that I want to use as well. Sorry if this seems to be a stupid question, but I am a very novice excel user

View 7 Replies View Related

Fill Cell With Single Character

Nov 27, 2006

I would like to enter an X or O into a square cell so that it completely fills the cell.

I want to eliminate the space between the text character and the cell border.

The character does not necessarily have to be text so long as it can be automatically entered into the cell by a macro and can also be deleted by a sub routine to refresh the worksheet for new data.

I created autoshapes for a cross and a circle but I have no idea how to substitute these for the text characters in the worksheet or delete them afterwards.

View 9 Replies View Related

Control That Allows Single Character Formatting

Oct 5, 2007

When I try to add the Richtextbox control, I get this error: "The control could not be created because it is not properly licensed." Since I'm in a corporate setting, I don't think I can get around this (since I don't have access to the computer's registry and several users will be using it). I have figured out a way to present the user with some rich text controls (Bold, Italics, Underline, Font, Color, etc), which will add special characters around the selected text which then is interpreted by a procedure and formats the text in the output destination (HTML document).

However, that tends to clutter up the textbox and makes it somewhat cumbersome for some users to edit the text. I'd prefer to have them see the formatted text in the textbox, if possible. So, is there a way to get some sort of Rich Text Box without the RichTextBox control? It's long shot, I know.

View 3 Replies View Related

Count Cells With Only One/Single Character

May 1, 2008

I need a formula that counts the number of cells in a range that only contains one character. Example, column A contains:


In the above, I want the result to be 2. I've tried =countif(a1:a6,len=1) but that doesn't work.

View 2 Replies View Related

Changing Default Option For Data Series So X & Y Axis

Oct 12, 2007

I'm trying to change the default option for the data series when graphing in Excel 2003 .... I am trying to create a line graph

Excel, by default, is putting static pressure on the x axis and the air flow on the y axis .... I need it to be the other way around

I have found out that:
By default, Excel plots whatever you have fewer ofrows or columnsas the data series

apparently it is possible to change this Excel default option, but I can't figure that out

in step 2 of the Chart Wizard, I have tried many different things, but can't get Excel to chart the Air Flow CFM on the x axis, and have static pressure values on the y axis

View 9 Replies View Related

Making Sure An Option Button Has Been Selected Within A Group Before Exiting Survey

Nov 26, 2009

I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.

View 2 Replies View Related

Text String To Single Character Array?

Jul 13, 2012

There is a method to use ROW(1:10) within an array formula to strip a string into individual elements e.g.

HELLO > {"H","E","L","L","O"}

I cannot find it anywhere, thought it was something like =MID(A1,ROW(1:10),1) but not yielding results.

View 4 Replies View Related

Compare Two Columns For Single Character Matches

May 20, 2008

I have two excel sheets exported from two sources of information. Both sheets contain 1 column which is the primary data, ie, there is no duplicate of that information. Each information in these column represent 1 device in the real world.

Now, for example:
In one sheet, I have: SDV0620B
In the other sheet, I have: SSDV-B0620B

Please note that, both data actually represent the same device. What I now need to make sure is that, I ccan identify each corresponding data from the two sheets.

So, here is the problem that I am facing, identifying two data which are same but has two different representation techniques. (since the data are from two different sources)

I came up with the idea that, if I can check the sequence of characters with one column to another, then I might be able to pull it off.

For example,
we take SDV0620B, and match the sequence of character with SSDV-B0620B.
therefore, first is S (from SDV0620B)
match with SSDV-B0620B
then, D
match SSDV-B0620B
then V
match SSDV-B0620B
and so on, until the end.

And if the 1st sequence is found in the second sequnce of characters, then it is called a match! Hope I have explained it clearly. I have provided a file with some of the data copy and pasted out, to give you an idea of what the values look like.

View 9 Replies View Related

Changing Groupname And Other Properties In Option Buttons From The Control Toolbox

May 31, 2006

Im trying to create many option buttons on my sheet and then change the GroupName and Name properties but im having no luck in accessing the properties. Here is the code ive got at the moment, could someone tell me how to access these properties. Shown below is a small section of the code, if you require the full code i will happily provide it. This is my first post and im not sure if this code tag thing has been done properly:

n = 18
Call Section_Header
Range("D" & n - 1).Value = " Case"
For i = 1 To UBound(Name_Case, 1)
Range("D" & n + i).Value = Name_Case(i)
Range("M" & n + i).Value = Cost_Case(i)
ActiveSheet. OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
Left:=628, Top:=(n - 1) * 15.75 + (1 * 6) + (i - 1) * 15.75, Width:=46.5, Height _
Next i

View 2 Replies View Related

Excel 2007 :: Find And Delete Single Character?

Jan 23, 2014

Following the tips doesn't allow to remove a character.

I exported email addresses from Outlook and they have the following character ' in front of the email address.

Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"

So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.

View 4 Replies View Related

Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box

Aug 13, 2012

Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.

Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names

if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown

The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.

View 3 Replies View Related

VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells

May 15, 2009

Looking for VBA that can change the font size of "" char(0149) within any string in selected cells.

Also looking to delete the last "" char(0149) within any string of selected cells.

What would the syntax for these two functions be?

View 9 Replies View Related

How To Trim Text In A Single Cell Base On Special Character In String

Dec 2, 2013

i have a device that produce its data as following:


I need to divide the single column to 7 column as separated by semicolon ";"

View 2 Replies View Related

Sum Selected Columns For Single Row

Aug 13, 2013

I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.

I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.

My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))

[Code] .........

View 2 Replies View Related

Changing Range Selected With Variable

Jan 17, 2009

I have some code that uses offset to select a column of numbers

View 2 Replies View Related

Changing Cell Value Based On Selected Row.

Jun 8, 2009

Is it possible to select a cell on any given row and the value of a cell on that row for example C5 & C6 are shown in cells A1 and B1 respectively? So if the user moved down the sheet selecting different rows the data would change?

View 4 Replies View Related

Changing Cell Values As Per The Value Selected In Dropdown?

Feb 4, 2014

I want a functionality in a sheet wherein if i select a value from a drop down, i will get specific fields to be entered in the sheet below and when i select a different value from the drop down the fields to be entered should change.

View 3 Replies View Related

How To Get Info From A Single Selected Graph Datapoint Using Right Click

Mar 9, 2014

I have an XY plot where the source X value data is a series of time values, each associated with a descriptive text cell. I would like to select a single point (obviously by mouse) and then execute a macro (right click preferred but a separate command button would be a suitable substitute for now) that will extract the X-value. I then plan to use that value to find within the source data the associated descriptive text cell and make a data label from that text. No clue where to start...the macro recorder is woefully deficient in this area.

View 2 Replies View Related

Update Single Column If Listbox Item Selected

Aug 1, 2014

How can I update column B for each item selected in a Listbox populated as below:

Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i

For each item selected in Listbox1, I want Column B to show "CONFIRMED"

View 3 Replies View Related

Copyrights 2005-15, All rights reserved