Whole words in drop down meny, but only first letter appear in cell

[littleme]

I was just wondering if there is a way to do the following:

Have created a drop down menu using data/validation. This menu is a
list of project names. What I would like is, when name is selected from
this menu, only the first letter of this name actually appears in the
cell. So if the choose, France, F should appear. Its important that the
whole words are available in the drop down.
Any ideas anyone?

would be grateful for help... =)

 

[Bernard Rey]

I was just wondering if there is a way to do the following:

Have created a drop down menu using data/validation. This menu is a
list of project names. What I would like is, when name is selected from
this menu, only the first letter of this name actually appears in the
cell. So if the choose, France, F should appear. Its important that the
whole words are available in the drop down.

One way could be to use an event macro. If you paste the lines hereunder in
the code sheet corresponding to the Worksheet on which you have the
validation, it'll change the content of the cell "C1" to its initial. To
open the code sheet, Control-click the sheet tab and select "View Code".

If the cell in which you have your project displayed is not "C1", you'll
have to adapt it to you situation. Note that it'll only be modified when you
change the selection in your sheet (which can be a problem).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

MyCountry = Range("C1").Value
MyInitial = Left(MyCountry, 1)
Range("C1").Value = MyInitial

End Sub

It would be possible to have an immediate result using the "Combo Box" item
in the "Forms" toolbar, but that would require some modifications in your
sheet, as it's not embedded in a cell and the result would need some
converting.

And, of course, there may be smarter solutions

 

[littleme]

Im sorry, I dont really understand....

Is MyCountry the Name of a Source (that is used to create a drop down)
or?
And what is MyInitial?

Sounds like an interesting solution that I have not yet tried... would
be very happy if you could clarify a bit =)
merry belated christmas btw

 

[Bernard Rey]

Im sorry, I dont really understand....

Is MyCountry the Name of a Source (that is used to create a drop down)
or? And what is MyInitial?

No, they are just variables. They could as well be named "Variable1" and
"Variable2" or any other suitable name. It has no importance in itself.

Consider the five lines as a lot:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

This one indicates that this is a macro that will be launched everythime the
selection changes in the sheet (like clicking in another cell or hitting the
"Enter" key). It will at the same time memorize the new selected cell (or
block of cells) as a "Range" in the variable named "Target" (we won't use it
in this macro, so you can forget about it for now).

MyCountry = Range("C1").Value

This line sets the value of a variable named MyCountry to the content of
cell C1 (let's say "France"). If C1 is not the cell in which you select the
country, just adapt the line to your needs.

MyInitial = Left(MyCountry, 1)

This line sets the value of the variable named MyInitial to the first
character left in the MyCountry variable. The first character left in
"France" will of course be "F".

Range("C1").Value = MyInitial

This line sets the content of cell C1 to MyInitial, thus displaying the
letter "F" where it read "France" a second earlier.

End Sub

This line ends the macro and gives the focus back to the user

Sounds like an interesting solution that I have not yet tried... would
be very happy if you could clarify a bit =)

Hope I did

 

[littleme]

It works perfectly!

Thank you!

Only one last small question... If I want this to work for all cells
between B3 and B32 and C3 to C32 as well, how do I adapt the code?
thanks once again...

nina

 

[Bernard Rey]

It works perfectly!

Thank you!

You're welcome

Only one last small question... If I want this to work for all cells
between B3 and B32 and C3 to C32 as well, how do I adapt the code?

Well, it's not a "small question" in fact.

The "easy" would be to change the macro this way:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

For Each c In Range("B3:C32")
MyCountry = c.Value
MyInitial = Left(MyCountry, 1)
c.Value = MyInitial
Next

End Sub

It's basically the same macro, running on each cell in the expected area.
But it's a rather "heavy" treatment and it may make things unnecessarily
slow, for sure.

The problem is that changing the value in a cell from a "Validation" list
isn't considered as a change in the Worksheet and doesn't generate an event.
And this make things difficult (and this is why the above macro uses the
event generated by the selection change to check through the cells).

Now, there could be different workarounds, like clicking on a button each
time you want to "downsize" the cells, or so. But it would be helpful to
know how the Worksheet runs in order to find out a better/easier way to deal
with it. If the sheet is only to be modified once in a while, you could use
the above variation, but if you have to change the countries every other
minute, you won't accept it...

Could you give a bit more information about it?

 

[littleme]

I think it should be ok. Tried it and seems fine =)

The worksheet is a time sheet for people working in an architecture
office. The valisation and "abbreviations" are meant to make it easier
to see how many hours theyve worked on what, (plans, sections, details
etc) So once filled in, they wont need to change the content of the
cell. I just wanted it to abbreviated because the time sheet has to fit
on an A4, and there isnt enought space for the entire word Presentation
to be written out....

Still havent tried it at work, but Im sure it will fine, but let me get
back to you on monday?

Youve been a huge help.

happy new year

nina

 

[Bernard Rey]

I think it should be ok. Tried it and seems fine =)

The worksheet is a time sheet for people working in an architecture
office. The valisation and "abbreviations" are meant to make it easier
to see how many hours theyve worked on what, (plans, sections, details
etc) So once filled in, they wont need to change the content of the
cell. I just wanted it to abbreviated because the time sheet has to fit
on an A4, and there isnt enought space for the entire word Presentation
to be written out....

In this case, I suppose it could be enough it it only runs when saving your
workbook. The principle is the same but, once you have opened the Visual
Basic Editor, double-click the "ThisWorkbook" icon thus opening the
corresponding code sheet.

In this sheet, paste the following lines:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").Range("B3:C32")
MyCountry = c.Value
MyInitial = Left(MyCountry, 1)
c.Value = MyInitial
Next
Application.ScreenUpdating = True
End Sub

And you should clear the macro lines in the other code sheet, in order to
get rid of it now. If not, you'll have the two of them running

As you can see, the "active lines" are the same, but it'll only run when you
save your Workbook, letting things run smoother the rest of the time.

Note that you could run it before printing your sheet instead, if you modify
the first line as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

 

[littleme]

Hello again =)

End up getting syntax error when I try to save. And realised that it
needs to update itself "on-screen" so to speak, otherwise people might
get confused and frustrated that the sheets looks so "messy", which it
does until the abbreviation appears. Used the original code you
suggested, but you were right about that one; takes way too long time.

So ive decided to make it easy and just make the drop down using
abbreviations, and then having a key. I give up on doind it another
way. Of course if you have any other ideas, Id be happy to hear them,
but for now I think im going to go for the easiest solution.
thanks anyway though, appreciate the effort =)

nina