Hello all,

I am facing a constant problem while creating a "thick box border" for
the merged cells for special conditions. I would deeply appreicate
anyone

here is a situation:

merged cell B2 (formed by merging cells B2 to B8) and another merged
cell C2 (formed by merging C2, D2, E2, F2) are in a worksheet.

merged cell B2 is clicked, Ctrl is pressed and the other merged cell C2
is pressed. This selects both the merged cells. When this operation is
performed and if the user presses a comman button "Show bounds", then
the area encompassed by these cells should turn into a thick box
border... Is this possible? If yes, please help me..

In this case, the result would be a thick box around the merged cell
B2, merged cell C2, cells F3-F8 and cells C8-F8.

Please help me with the code, that is generic and works with any type
of merging of cells.

Thanks a lot,
Thulasiram

'Adds the border.
Function OutlineSelectedAreas(ByRef Rng1 As Excel.Range, _
ByRef Rng2 As Excel.Range)
'Jim Cone - San Francisco, USA - October 2006
Dim Rng3 As Excel.Range
Dim lngRow As Long
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long

lngRow = Application.Min(Rng1.Row, Rng2.Row)
lngCol = Application.Min(Rng1.Column, Rng2.Column)
lngLastRow = Application.Max(Rng1.Rows(Rng1.Rows.Count).Row, _
Rng2.Rows(Rng2.Rows.Count).Row)
lngLastCol = Application.Max(Rng1.Columns(Rng1.Columns.Count).C olumn, _
Rng2.Columns(Rng2.Columns.Count).Column)

Set Rng3 = Range(Cells(lngRow, lngCol), Cells(lngLastRow, lngLastCol))
Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
Set Rng1 = Nothing
Set Rng2 = Nothing
Set Rng3 = Nothing
End Function
'------------------
'Calls function after two areas selected.
Sub DoTheBorders()
'Jim Cone - San Francisco, USA - October 2006
Dim rngStart As Excel.Range
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range

Set rngStart = Excel.Selection
If rngStart.Areas.Count < 2 Then
MsgBox "Select two areas. "
Else
Set rngOne = rngStart.Areas(1)
Set rngTwo = rngStart.Areas(2)
Call OutlineSelectedAreas(rngOne, rngTwo)
End If
Set rngStart = Nothing
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Thulasiram"
wrote in message
Hello all,
I am facing a constant problem while creating a "thick box border" for
the merged cells for special conditions. I would deeply appreicate
anyone here is a situation:
merged cell B2 (formed by merging cells B2 to B8) and another merged
cell C2 (formed by merging C2, D2, E2, F2) are in a worksheet.

merged cell B2 is clicked, Ctrl is pressed and the other merged cell C2
is pressed. This selects both the merged cells. When this operation is
performed and if the user presses a comman button "Show bounds", then
the area encompassed by these cells should turn into a thick box
border... Is this possible? If yes, please help me..

In this case, the result would be a thick box around the merged cell
B2, merged cell C2, cells F3-F8 and cells C8-F8.

Please help me with the code, that is generic and works with any type
of merging of cells.
Thanks a lot,
Thulasiram

Jim............................................... .

I really dont have any words to praise you. THANKS THANKS THANKS A
LOT..............

I have been struggling with this for the past two days and finally
didnt have any option but to ask help in this wonderful group.

I have one more question.

How to undo that process? i.e. after visualzing the border, if the the
command button "Hide border" is clicked, the border should be
invisible.

I was trying to manipulate this line, inorder to make the line
invisible.

Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium

I am unfamiliar with the syntax involved in the bordering scheme.

Please help Jim.

Thanks a ton,
Thulasiram




Jim Cone wrote:
'Adds the border.
Function OutlineSelectedAreas(ByRef Rng1 As Excel.Range, _
ByRef Rng2 As Excel.Range)
'Jim Cone - San Francisco, USA - October 2006
Dim Rng3 As Excel.Range
Dim lngRow As Long
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long

lngRow = Application.Min(Rng1.Row, Rng2.Row)
lngCol = Application.Min(Rng1.Column, Rng2.Column)
lngLastRow = Application.Max(Rng1.Rows(Rng1.Rows.Count).Row, _
Rng2.Rows(Rng2.Rows.Count).Row)
lngLastCol = Application.Max(Rng1.Columns(Rng1.Columns.Count).C olumn, _
Rng2.Columns(Rng2.Columns.Count).Column)

Set Rng3 = Range(Cells(lngRow, lngCol), Cells(lngLastRow, lngLastCol))
Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
Set Rng1 = Nothing
Set Rng2 = Nothing
Set Rng3 = Nothing
End Function
'------------------
'Calls function after two areas selected.
Sub DoTheBorders()
'Jim Cone - San Francisco, USA - October 2006
Dim rngStart As Excel.Range
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range

Set rngStart = Excel.Selection
If rngStart.Areas.Count < 2 Then
MsgBox "Select two areas. "
Else
Set rngOne = rngStart.Areas(1)
Set rngTwo = rngStart.Areas(2)
Call OutlineSelectedAreas(rngOne, rngTwo)
End If
Set rngStart = Nothing
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Thulasiram"
wrote in message
Hello all,
I am facing a constant problem while creating a "thick box border" for
the merged cells for special conditions. I would deeply appreicate
anyone here is a situation:
merged cell B2 (formed by merging cells B2 to B8) and another merged
cell C2 (formed by merging C2, D2, E2, F2) are in a worksheet.

merged cell B2 is clicked, Ctrl is pressed and the other merged cell C2
is pressed. This selects both the merged cells. When this operation is
performed and if the user presses a comman button "Show bounds", then
the area encompassed by these cells should turn into a thick box
border... Is this possible? If yes, please help me..

In this case, the result would be a thick box around the merged cell
B2, merged cell C2, cells F3-F8 and cells C8-F8.

Please help me with the code, that is generic and works with any type
of merging of cells.
Thanks a lot,
Thulasiram

Have the user do the following...
Select the area
Format menu | Cells | Border (tab)
Click the "None" button.

Record a macro if you want some code.
"Selection.Borders.LineStyle = xlNone" will work most of the time.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Thulasiram"
wrote in message
Jim............................................... .
I really dont have any words to praise you.
THANKS THANKS THANKS A LOT..............
I have been struggling with this for the past two days and finally
didnt have any option but to ask help in this wonderful group.
I have one more question.
How to undo that process? i.e. after visualzing the border, if the the
command button "Hide border" is clicked, the border should be
invisible.
I was trying to manipulate this line, inorder to make the line
invisible.
Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
I am unfamiliar with the syntax involved in the bordering scheme.
Please help Jim.
Thanks a ton,
Thulasiram

Jim,

I understand the advantages of recording macros. But I feel that the
disadvantage of macros is it is not generic. I recorded a macro (given
below) as you said. It works fine. But, it works for that particular
range "B2:F8". If I record macros for all sets, then hiding the
boundaries doesnt become generic.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/9/2006 by Thulasiram
'
' Keyboard Shortcut: Ctrl+h
'
Range("B2:F8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

I tried using "Selection.Borders.LineStyle = xlNone" in a different
function called as OutlineSelectedAreas1 but unfortunately it did not
hide the border :-(

Jim Cone wrote:
Have the user do the following...
Select the area
Format menu | Cells | Border (tab)
Click the "None" button.

Record a macro if you want some code.
"Selection.Borders.LineStyle = xlNone" will work most of the time.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Thulasiram"
wrote in message
Jim............................................... .
I really dont have any words to praise you.
THANKS THANKS THANKS A LOT..............
I have been struggling with this for the past two days and finally
didnt have any option but to ask help in this wonderful group.
I have one more question.
How to undo that process? i.e. after visualzing the border, if the the
command button "Hide border" is clicked, the border should be
invisible.
I was trying to manipulate this line, inorder to make the line
invisible.
Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
I am unfamiliar with the syntax involved in the bordering scheme.
Please help Jim.
Thanks a ton,
Thulasiram

Just remove the Range("B2:F8").Select line.
--
Jim Cone



"Thulasiram"
wrote in message
Jim,
I understand the advantages of recording macros. But I feel that the
disadvantage of macros is it is not generic. I recorded a macro (given
below) as you said. It works fine. But, it works for that particular
range "B2:F8". If I record macros for all sets, then hiding the
boundaries doesnt become generic.

Sub Macro2()
' Macro2 Macro
' Macro recorded 10/9/2006 by Thulasiram
'
' Keyboard Shortcut: Ctrl+h
'
Range("B2:F8").Select '<<<<<<<<<<<REMOVE
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

I tried using "Selection.Borders.LineStyle = xlNone" in a different
function called as OutlineSelectedAreas1 but unfortunately it did not
hide the border :-(



Jim Cone wrote:
Have the user do the following...
Select the area
Format menu | Cells | Border (tab)
Click the "None" button.

Record a macro if you want some code.
"Selection.Borders.LineStyle = xlNone" will work most of the time.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

Jim,

I am in a problem now. Its not with respect to hiding the boundaries.
Problem is in continuation of the previous scenario.

I have a merged cell G2 (formed by merging cells G2 to J8) adjcent to
the merged cell C2 and another merged cell B9 (formed by merging B9 to
B14) below the merged cell B2.

When I click the cells G2 and B9 and hit "show bounds" command button
(that has your code), I have the boundary around the cells B2-B14,
B14-J14, J14-J2, J2-B2

But the expected result should be a thick boundary around the cells
G9-G14, G14-J14, J14-J9, J9 -G9.

Please help. It will be so nice of you.

Thanks,
Thulasiram


Jim Cone wrote:
Have the user do the following...
Select the area
Format menu | Cells | Border (tab)
Click the "None" button.

Record a macro if you want some code.
"Selection.Borders.LineStyle = xlNone" will work most of the time.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Thulasiram"
wrote in message
Jim............................................... .
I really dont have any words to praise you.
THANKS THANKS THANKS A LOT..............
I have been struggling with this for the past two days and finally
didnt have any option but to ask help in this wonderful group.
I have one more question.
How to undo that process? i.e. after visualzing the border, if the the
command button "Hide border" is clicked, the border should be
invisible.
I was trying to manipulate this line, inorder to make the line
invisible.
Rng3.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
I am unfamiliar with the syntax involved in the bordering scheme.
Please help Jim.
Thanks a ton,
Thulasiram

Great Jim!

It works. You solved this hiding issue!

Thanks a lot.

Jim Cone wrote:
Just remove the Range("B2:F8").Select line.
--
Jim Cone



"Thulasiram"
wrote in message
Jim,
I understand the advantages of recording macros. But I feel that the
disadvantage of macros is it is not generic. I recorded a macro (given
below) as you said. It works fine. But, it works for that particular
range "B2:F8". If I record macros for all sets, then hiding the
boundaries doesnt become generic.

Sub Macro2()
' Macro2 Macro
' Macro recorded 10/9/2006 by Thulasiram
'
' Keyboard Shortcut: Ctrl+h
'
Range("B2:F8").Select '<<<<<<<<<<<REMOVE
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

I tried using "Selection.Borders.LineStyle = xlNone" in a different
function called as OutlineSelectedAreas1 but unfortunately it did not
hide the border :-(



Jim Cone wrote:
Have the user do the following...
Select the area
Format menu | Cells | Border (tab)
Click the "None" button.

Record a macro if you want some code.
"Selection.Borders.LineStyle = xlNone" will work most of the time.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

You have changed the rules.
Now you are trying to exclude a selected area.
I can't help you.
--
Jim Cone

"Thulasiram"
wrote in message
Jim,
I am in a problem now. Its not with respect to hiding the boundaries.
Problem is in continuation of the previous scenario.

I have a merged cell G2 (formed by merging cells G2 to J8) adjcent to
the merged cell C2 and another merged cell B9 (formed by merging B9 to
B14) below the merged cell B2.

When I click the cells G2 and B9 and hit "show bounds" command button
(that has your code), I have the boundary around the cells B2-B14,
B14-J14, J14-J2, J2-B2
But the expected result should be a thick boundary around the cells
G9-G14, G14-J14, J14-J9, J9 -G9.
Please help. It will be so nice of you.
Thanks,
Thulasiram

Oops.. that's unfortunate. Please help me if you feel so... I am trying
to make it work.. but helpless

Sorry about my change in rules..

Thanks,
Thulasiram.

On Oct 9, 10:42 pm, "Jim Cone" wrote:
You have changed the rules.
Now you are trying to exclude a selected area.
I can't help you.
--
Jim Cone

"Thulasiram"
wrote in message
Jim,
I am in a problem now. Its not with respect to hiding the boundaries.
Problem is in continuation of the previous scenario.

I have a merged cell G2 (formed by merging cells G2 to J8) adjcent to
the merged cell C2 and another merged cell B9 (formed by merging B9 to
B14) below the merged cell B2.

When I click the cells G2 and B9 and hit "show bounds" command button
(that has your code), I have the boundary around the cells B2-B14,
B14-J14, J14-J2, J2-B2
But the expected result should be a thick boundary around the cells
G9-G14, G14-J14, J14-J9, J9 -G9.
Please help. It will be so nice of you.
Thanks,
Thulasiram

In Vba help, look for the "Intersect" method...
"Returns a Range object that represents the rectangular intersection of two or more ranges."
Jim Cone


"Thulasiram"
wrote in message
Oops.. that's unfortunate. Please help me if you feel so... I am trying
to make it work.. but helpless
Sorry about my change in rules..
Thanks,
Thulasiram.


On Oct 9, 10:42 pm, "Jim Cone"
wrote:
You have changed the rules.
Now you are trying to exclude a selected area.
I can't help you.
--
Jim Cone

"Thulasiram"
wrote in message
Jim,
I am in a problem now. Its not with respect to hiding the boundaries.
Problem is in continuation of the previous scenario.

I have a merged cell G2 (formed by merging cells G2 to J8) adjcent to
the merged cell C2 and another merged cell B9 (formed by merging B9 to
B14) below the merged cell B2.

When I click the cells G2 and B9 and hit "show bounds" command button
(that has your code), I have the boundary around the cells B2-B14,
B14-J14, J14-J2, J2-B2
But the expected result should be a thick boundary around the cells
G9-G14, G14-J14, J14-J9, J9 -G9.
Please help. It will be so nice of you.
Thanks,
Thulasiram

Thanks Jim. I will have a look at it.

-Thulasiram

Jim Cone wrote:
In Vba help, look for the "Intersect" method...
"Returns a Range object that represents the rectangular intersection of two or more ranges."
Jim Cone


"Thulasiram"
wrote in message
Oops.. that's unfortunate. Please help me if you feel so... I am trying
to make it work.. but helpless
Sorry about my change in rules..
Thanks,
Thulasiram.


On Oct 9, 10:42 pm, "Jim Cone"
wrote:
You have changed the rules.
Now you are trying to exclude a selected area.
I can't help you.
--
Jim Cone

"Thulasiram"
wrote in message
Jim,
I am in a problem now. Its not with respect to hiding the boundaries.
Problem is in continuation of the previous scenario.

I have a merged cell G2 (formed by merging cells G2 to J8) adjcent to
the merged cell C2 and another merged cell B9 (formed by merging B9 to
B14) below the merged cell B2.

When I click the cells G2 and B9 and hit "show bounds" command button
(that has your code), I have the boundary around the cells B2-B14,
B14-J14, J14-J2, J2-B2
But the expected result should be a thick boundary around the cells
G9-G14, G14-J14, J14-J9, J9 -G9.
Please help. It will be so nice of you.
Thanks,
Thulasiram