Miscellaneous VBA questions

[Scott]

Hi,

(I'm not a VBA programmer...sorry for the basic questions)

I first searched this group for "uppercase", since it's clearly a
FAQ

I found:

http://groups.google.com.au/group/m...667b3d3d?lnk=gst&q=uppercase#43afb94a667b3d3d,
and

http://www.cpearson.com/Excel/ChangingCase.aspx

So far, so good. But I have a few questions:

1. Can I define the function in the "This Workbook" module, then
write a "wrapper function" for each worksheet needing the data
validation? For example, if I name Chip's function "SetUppercase",
then the wrapper function might be:

Private Sub Worksheet_Change(ByVal Target As Range)
SetUppercase <<< I need the syntax for ThisWorksheet!
SetUppercase, or does it look in ThisWorksheet automatically?
End Sub

for each worksheet needing the validation.

2. Can I enter the range as a parameter to the function? This then
becomes:

Private Sub Worksheet_Change(ByVal Target As Range)
SetUppercase(A1:A10)
End Sub

I need the syntax to specify the range as a parameter.

3. Can I specify a range as "all cells in the column"? Does the
range then become "A:A"?

4. Finally, can I specify multiple columns in the range, i.e. columns
A, C, E? Does the range then become "A:A,C:C,E:E"? Or do I need to
parse the range somehow in the code?

My actual problem definition is about 30 worksheets in a workbook,
with a subset of these worksheets needing data validation (values
converted to all uppercase) for a subset of their columns, the list of
which varies for each worksheet.

Thanks a lot!

Scott

P.S.: If you have any favorite web links for Excel programming,
please paste them in and I'll read/bookmark them.

 

[Joel]

The worksheet change function is only used when an item is entered into a
wrokbook. If you have a workbook with data already entered then you use a
subroutine (not function) to change the lower case strings to upper case. If
you give the details of which cells need to be changed. Here is a simple case

Sub MakeUpper()

for each sht in sheets
sht.Range("A1") = UCase(sht.Range("A1"))
sht.Range("B4:B10") = UCase(sht.Range("B4:B10"))
sht.Range("E8") = UCase(sht.Range("E8"))
next sht


End Sub