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 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.

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.

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.

 

[Bob Phillips]

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 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.

 

[Scott]

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.




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


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


As per point 3.


Your case statement can check different Target ranges for different sheets.- Hide quoted text -

- Show quoted text -

Thanks Bob for the reply. However, I think this approach would
capitalize all values, based on whether I had changed it. That's not
quite what I want to do.

I pseudocode, what I want to do is:

* define the "main" subroutine in one spot (ThisWorkbook)
* For Sheet1, capitalize columns A, C, and E. All other columns
should remain as entered.
* For Sheet2, do nothing.
* For Sheet3, capitalize columns B-E. All other columns should remain
as entered.

And I was thinking that, rather than coding all this logic in the
ThisWorkbook subroutine (check for which worksheet and which columns),
I would just define a generic subroutine in ThisWorkbook, then call
that subroutine from the Workbook_SheetChange for each worksheet that
requires the validation.

Thanks,
Scott

 

[Bob Phillips]

This approach capitalizes only that that you change, not everything. Try it
and see..

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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.




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


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


As per point 3.


Your case statement can check different Target ranges for different
sheets.- Hide quoted text -

- Show quoted text -

Thanks Bob for the reply. However, I think this approach would
capitalize all values, based on whether I had changed it. That's not
quite what I want to do.

I pseudocode, what I want to do is:

* define the "main" subroutine in one spot (ThisWorkbook)
* For Sheet1, capitalize columns A, C, and E. All other columns
should remain as entered.
* For Sheet2, do nothing.
* For Sheet3, capitalize columns B-E. All other columns should remain
as entered.

And I was thinking that, rather than coding all this logic in the
ThisWorkbook subroutine (check for which worksheet and which columns),
I would just define a generic subroutine in ThisWorkbook, then call
that subroutine from the Workbook_SheetChange for each worksheet that
requires the validation.

Thanks,
Scott

 

[Scott]

Hi Bob,

I will try it and see when I get a chance (juggling a number of things
right now...)

The Excel worksheet is being used as a quasi-database, in fact it is
imported into a database via an ETL process.

The "key" values need to be capitalized, other values, such as
"description" should be left alone.

For example, if my worksheet has the columns (row 1):

KEY DESCRIPTION
FOO This is Foo
BAR This is Bar
BLAH This is Blah

If the user then enters:

fubar This is FUBAR

I want fubar changed to FUBAR, and "This is FUBAR" left alone.

However, since "This is FUBAR" constitutes a change, as I understand
it your code would capitalize it, which is not what I want.

Thanks,
Scott