MACRO HELP

[MrDave]

hi is there a way to add multiple ranges to a single macro, same as saying
"OR",
example, for if you enter lower case in different / specific locations, the
will be capitalized (e.g.: columns A F X, top section cells: $A$5, $M$5)
thanks

macro have is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then

'If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Per Jessen]

Hi

I think this is what you want:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set isect = Intersect(Target, Range("A1, M1, X1"))
If Not isect Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
End If
'ErrHandler:
Application.EnableEvents = True
End Sub

Regards,
Per

 

[MrDave]

hi, I gave that a try, but problem might be technique I use for Dim Ranges
to make macro dynamic for row / column changes. below has that info and
error i received. only problem might be as listed for isect: error variable
not defined
how do I define that, thanks,


Dim testB1 As String 'caps
testB1 = Range("B1")
'cell has: =SUBSTITUTE(SUBSTITUTE(CELL("address",$DY$4),"$",""),"","")
'for columns:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$EL2),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$EL2),"$",""),ROW(),"")


'SAMPLE of what use, that works
If Range(testB1).Value = "D" Then

If Range(testP6).Value = "1" Then '1st DL, clear p2-5
Columns(colPALL1).Select
Selection.ClearContents
End If



Set isect = Intersect(Target, Range("testB1, G1, G2, G3, G4, G5, G6, G7"))
'error variable not defined

'Set isect = Intersect(Target, Range("A1, M1, X1")) 'orig sample
If Not isect Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
End If
'ErrHandler:
Application.EnableEvents = True
End Sub

 

[MrDave]

a second test, in removing the quotes, think as should be for defined ranges,
gets a different error.

'im testB1 As String
testB1 = Range("B1") 'caps


Set isect = Intersect(Target, Range(testB1, G1, G2, G3, G4, dateC4))
'error variable not defined
'without quotes: wrong number of arguments, or invalid property assignment

'Set isect = Intersect(Target, Range("A1, M1, X1")) 'orig sample
If Not isect Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
End If
'ErrHandler:
Application.EnableEvents = True
End Sub

 

[MrDave]

sorry, 1 letter typo (for 'im, Dim)

Dim testB1 As String
testB1 = Range("B1") 'caps


Set isect = Intersect(Target, Range(testB1, G1, G2, G3, G4, dateC4))
'error variable not defined
'without quotes: wrong number of arguments, or invalid property assignment

'Set isect = Intersect(Target, Range("A1, M1, X1")) 'orig sample
If Not isect Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
End If
'ErrHandler:
Application.EnableEvents = True
End Sub

 

[Per Jessen]

Hi


Try this, I am not sure if dateC4 is an named range or a variable, so
I left it out.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim testB1 As String
testB1 = Range("B1") 'caps

MsgBox testB1 & ", G1, G2, G3, G4"
Set isect = Intersect(Target, Range(testB1 & ", G1, G2, G3, G4")) ',
dateC4"

If Not isect Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
End If
'ErrHandler:
Application.EnableEvents = True
End Sub

Hopes this helps.
....
Per

 

[MrDave]

Hi, finally got around to checking this out, seemed to get to work with the
variables you picked: 1 name range, and separate cells. (I'm not that quick
with macro's, so to describe..) I put this variation on a single sheet, but
was not able to get mulitple / separate named ranges to work, got an error,
is there syntax I am missing on its entry? thanks

(note, all items were for separate Named Ranges, residing in G1 G2 .. but
as separate cells is good to know; those cells contain formula for implied
columns)


what tried: 2nd line not working, need separate named ranges, is that
possible?

Dim test1 As String
test1 = Range("B1")
Dim G3 As String
Dim G4 As String
G3 = Range("G3")
G4 = Range("G4")


'If Not Intersect(Target, Range(test1 & ", E2, E3, E4")) Is Nothing Then
If Not Intersect(Target, Range(test1 & G3 & G4)) Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False
Target = UCase(Target)
End With
Application.EnableEvents = True
End If

xxxxxx
sample in G3: for making changes in column CT

=SUBSTITUTE(SUBSTITUTE(CELL("address",$CT3),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$CT3),"$",""),ROW(),"")

xxxxxx

 

[MrDave]

Found a page that might help, researching..

http://support.microsoft.com/kb/291308

 

[MrDave]

hi, for anyone wanting a Uppercase / Ucase macro that seem to work, for
multiple named ranges, I have the following examples, thanks:

these are named ranges / cell G2 has the following formula
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CQ2),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$CQ2),"$",""),ROW(),"")

toprowid:
=ROW($A$180)

'CAPS

Dim toprowid As String
toprowid = Range("D6")
Dim G2 As String
G2 = Range("G2")
Dim G3 As String
G3 = Range("G3")


With Target
If .Count > 1 Then Exit Sub
If Target.Row < topID Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub


If Not Intersect(Target, Range(G2 & "," & G3)) Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
End With
Application.EnableEvents = True
End If

'CAPS NOT

If Not Intersect(Target, Range(G4 & "," & J2)) Is Nothing Then
With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
Application.EnableEvents = False
Target = LCase(Target)
End With
Application.EnableEvents = True
End If

End If
End If
End If
End With

 

[MrDave]

sorry, typo, toprowid / topid names should line up the same