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

[XiniX]

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?

[gareth]

Try out this download,its free



http://www.asap-utilities.com

check it out,then tell me if you still need a code to perform this duty.

[XiniX]

Will check it out, thank you...

Real cool, fast service...

[XiniX]

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...

[kinkyparamour]

Try using =upper(b1)where your text is in b1.

Denny

[Jay Petrulis]

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.

[Aladin Akyurek]

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 .

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

[Mark O'Brien]

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

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:

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

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 )

[XiniX]

Thank you Jay, You are super...

[ This Message was edited by: XiniX on 2002-05-02 06:42 ]

[Aladin Akyurek]

On 2002-05-02 06:36, Mark O'Brien wrote:

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:

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

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 [img]/board/images/smiles/icon_biggrin.gif[/img] )

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