Re: Miscellaneous VBA Questions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Scott" <usenet739@xxxxxxxxxxxx> wrote in message
news:74ed9ba3-43a2-4901-a8f5-5081808bc2cc@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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/microsoft.public.excel.misc/browse_thread/thread/853211446ff498f1/43afb94a667b3d3d?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 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.


.

Relevant Pages

  • Copying Cell Conflict with Worksheet Change to UpperCase
    ... I am using the following subroutine ... When I disable the Private Sub Worksheet_Change(ByVal Target As Range), ... Could someone please help as I still want to use Upper Case upon cell ...
    (microsoft.public.excel.programming)
  • Re: conditionally hiding rows
    ... Microsoft MVP Excel ... use View code and paste this subroutine ... Private Sub Worksheet_Change(ByVal Target As Range) ...
    (microsoft.public.excel.misc)
  • Re: conditionally hiding rows
    ... use View code and paste this subroutine ... Private Sub Worksheet_Change(ByVal Target As Range) ... Bernard V Liengme ...
    (microsoft.public.excel.misc)
  • Re: Links a cell to code
    ... Hi Fernando, ... Private Sub Worksheet_SelectionChange(ByVal Target As Range) ... The code above calls a different subroutine if the target address is ...
    (microsoft.public.excel.programming)
  • Re: Very Novice Excel user with security question
    ... sheet for easy editing instead of just a few select cells with a double click ... a password for the cell if it is not in order to change it. ... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, ...
    (microsoft.public.excel.misc)