Neater code and - F8 Step Into Query

[JW]

Hi,

A couple of things have come up today. I'm using Office 2003 and have set up a spreadsheet that needs users to input either a 'B' or 'S' in row H, and so I've set up a validated text box. I wanted to also put a little piece of code to change any manually input lower case 's' or 'b' to upper case, so I used:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

'UPPER CASE
' Loop to cycle through each cell in the specified range.
For Each x In Range("H16:H1016")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next

This wasn't really what I wanted as the loop is too large. Can anyone tell me how I can best rewrite this range to just include the target cell? Though would I need to include the cell underneath in the range for when the data is entered (and the activecell drops to the next row)?

And I have a problem using F8 in the VBE, and also 'Step Into' from the menu. I can't get the debug to work, except by generating bad code, and so can't check if my code is working or not (unless it's bad). Has anyone come across this in Office 2003? I've trawled Google, so far to no avail.

Thanks for any help,
JW

 

[Cbrine]

Why not just add this code to your textbox change event. It will force the input to upper case characters.

Private Sub TextBox1_Change()
TextBox1.Value = UCase(TextBox1.Value)
End Sub

HTH
Cal

 

[JW]

Thanks for the reply, but I wasn't being clear enough - there are text boxes in cells H16:H1016, and I'd like each entry to be capitalised if the user enters a value in lower case.

btw Is there one command that can force the entire range H16:H1016 into uppercase, or indeed can this range be formatted into upper case?

 

[tbird]

http://www.asap-utilities.com/index.php?page=p_ru.php

This is the ASAP Utility site from where you can download a free AddIn that contains a case-conversion utility among many others.

 

[Cbrine]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range

Set R = Intersect(Target, Range("H16:H1016"))

If Not (R Is Nothing) Then
    R.Value = UCase(R.Value)
End If
Set R = Nothing
End Sub

This code added to the worksheet level in the vba editor will cause all entries in range H16:H1016, to automatically change to Upper Case.
HTH
Cal

 

[JW]

Thanks again Cal, that works though the code does still leave me with
a couple of issues to sort -
1) It take over the focus for a couple of seconds which I guess is down to the range size. I'll look to tweak the range to cover the immediate row only. And
2) It doesn't like me deleting multiple entries, so I'll look at that before deciding whether to let it out with this extra functionality. Is there a quick line that says to ignore if target selection is more than one cell?

And thanks for the nod to ASAP tbird, though when I looked at their case functions it seemed that I could only change text after the event, rather than on an ongoing basis, so that wouldn't suit here.

 

[JW]

I'd like to keep the range down to the current and previous rows, but am repeatly getting stuck. Assuming JW and JW1 are Variants, this now fails on the 'Set R =' line. Can anyone see where I might be going wrong here?
(Looking to capitalise manual entries into Column H)

Thanks
JW

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range
Dim RW, RWup1 As Integer
Dim JW, JWup1 As Variant


RW = ActiveCell.Row
RWup1 = RW - 1

JW = Cells(RW, 8)
JWup1 = Cells(RWup1, 8)

Set R = Intersect(Target, Range("JWup1:JW"))

If Not (R Is Nothing) Then
R.Value = UCase(R.Value)
End If
Set R = Nothing

End Sub

 

[Cbrine]

Set R = Intersect(Target, Range(JWup1,JW))
The quotes are the problem.

Cal