"Scott" wrote in message
...
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/mi...b94a667b3 d3d,

and

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

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

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

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

for each worksheet needing the validation.


ThisWorbook already has such global events

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Sh.Name

Case "Sheet1", "Sheet3" 'etc

Target.Value = UCase(Target.Value)

Case Else
End Select
End Sub

In this you can check the sheet and act accordingly.


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) <<< Parameter to SetUppercase
End Sub

I need the syntax to specify the range as a parameter in the
SetUppercase subroutine.


You already have it, Target refers to the range being changed.


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


Do this by selecting the whole column and making the change.


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?


As per point 3.


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.


Your case statement can check different Target ranges for different sheets.