Can i make the result of a text-input uppercase in a cell?

XiniX

Board Regular
Joined
May 1, 2002
Messages
57
I have a sheet where users must input two letters of the postal-code, and the result must be uppercase regardless what the keyboard settings are at the moment of the input. Is there a ucase Cell preset-setting of that column, or do i have to build it into a macro after the input is done. Can anyone help me with this problem?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I downloaded it, and installed it...
And it works...

But now it works for my computer. But now it has to run on any computer...

So I have to find my own solution still...
 
Upvote 0
Hi,

For an automatic solution, use a worksheet_change event.

In a sheet module, not a regular code module, place the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 2 Then Target = UCase(Target)
End Sub

Change the target.column to your column. It is 2 (= column B) here.
 
Upvote 0
On 2002-05-02 05:56, Jay Petrulis wrote:
Hi,

For an automatic solution, use a worksheet_change event.

In a sheet module, not a regular code module, place the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 2 Then Target = UCase(Target)
End Sub

Change the target.column to your column. It is 2 (= column B) here.

I was just trying to figure out how to do this Worksheet_Change thing. Thanks for ruining my first faux pas into WBA :biggrin:.

I have a question though: Is it possible to variablize/parameterize the target column, for example, forcing the code to read from a cell in a worksheet called Admin?

Aladin
 
Upvote 0
I was just trying to figure out how to do this Worksheet_Change thing. Thanks for ruining my first faux pas into WBA :biggrin:.

I have a question though: Is it possible to variablize/parameterize the target column, for example, forcing the code to read from a cell in a worksheet called Admin?

Aladin

Well, well, well. Crossing over to the dark side are we? Wonders will never cease!

If I'm following you correctly, you want to be able to read data from a specific cell from a sheet named "Admin". (we'll use the old favourite cell, "A1")

Let's say, for example, whenever a value in column A on Sheet1 changed, we want to put the data from "Admin!A1" into "Sheet1!B1"

We would do this:

<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Exit the routine if more than one cell is changing
'This usually leads to horrible errors otherwise

If Target.Column = 1 Then
Sheets("Sheet1").Range("B1").Value = Sheets("Admin").Range("A1").Value
End If
End Sub</pre>

If I didn't pick up your example properly, just repost. Basically, anything you can do in a spreadsheet using Excel functions can be accomplished using VBA. (the trick is to find out how to do it :biggrin: )
 
Upvote 0
Thank you Jay, You are super...
This message was edited by XiniX on 2002-05-02 06:42
 
Upvote 0
On 2002-05-02 06:36, Mark O'Brien wrote:
I was just trying to figure out how to do this Worksheet_Change thing. Thanks for ruining my first faux pas into WBA /board/images/smiles/icon_biggrin.gif.

I have a question though: Is it possible to variablize/parameterize the target column, for example, forcing the code to read from a cell in a worksheet called Admin?

Aladin

Well, well, well. Crossing over to the dark side are we? Wonders will never cease!

If I'm following you correctly, you want to be able to read data from a specific cell from a sheet named "Admin". (we'll use the old favourite cell, "A1")

Let's say, for example, whenever a value in column A on Sheet1 changed, we want to put the data from "Admin!A1" into "Sheet1!B1"

We would do this:

<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Exit the routine if more than one cell is changing
'This usually leads to horrible errors otherwise

If Target.Column = 1 Then
Sheets("Sheet1").Range("B1").Value = Sheets("Admin").Range("A1").Value
End If
End Sub</pre>

If I didn't pick up your example properly, just repost. Basically, anything you can do in a spreadsheet using Excel functions can be accomplished using VBA. (the trick is to find out how to do it /board/images/smiles/icon_biggrin.gif )

Mark,

If I didn't pick up your example properly, just repost.

Alas, you didn't :).

I want Jay's code to read from Admin!A1 which column it should watch for user input and upper the inputted values.

What modification would the dark side apply to that code?

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top