Neater code and - F8 Step Into Query

JW

Board Regular
Joined
Mar 17, 2002
Messages
72
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Why not just add this code to your textbox change event. It will force the input to upper case characters.

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

HTH
Cal
 

JW

Board Regular
Joined
Mar 17, 2002
Messages
72
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?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

Code:
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

Board Regular
Joined
Mar 17, 2002
Messages
72
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

Board Regular
Joined
Mar 17, 2002
Messages
72
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Set R = Intersect(Target, Range(JWup1,JW))
The quotes are the problem.

Cal
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,565
Members
414,389
Latest member
MarkElla

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
Top