I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:
I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:

In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:
I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:
Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:


In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:

I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:

Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:
Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:


In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:

I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

CSorry Rowan, I just noticed that cell C9 is a merged cell of C9:D9, could
that be the problem?
Dori

"DORI" wrote:

Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:

Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:
Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:


In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:

I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:
Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:


Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:

Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:



In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:


I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:

Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:
Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:


Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:

Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:



In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:


I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:
Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:


Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:

Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:



Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:


Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:




In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:



I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Rowan,
Still not working, I think I am doing something wrong as when I try to run
the sub it does not recognize the macros you gave me. I created the 2 macros
(ClearBorders and CreateBorders) by going to VB Editor and then Insert/Module
and then put the code there and closed it. I can see the module 5 is there
with the macros but when I try to run the sub it jumbs to another module
(Module4) and I can see now that it can not find the macros there. What am I
doing wrong?
Thanks again for your patience Rowan,
Dori

"Rowan Drummond" wrote:

Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:
Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:


Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:

Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:



Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:


Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:




In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:



I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Dori

Forget the other macros for now - in fact delete them and remove module
5 completely.

Now right click on sheet1 and select view code. Delete any
worksheet_change event that is currently on that sheet. Then paste this
event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now open the immediate window (Ctrl+G) and type the line:
Application.EnableEvents = True
Then hit enter.
Return to excel and in Sheet1 cell C9 select 1 from the dropdown. Do you
get a message saying CreateBorders?

Regards
Rowan

DORI wrote:
Hi Rowan,
Still not working, I think I am doing something wrong as when I try to run
the sub it does not recognize the macros you gave me. I created the 2 macros
(ClearBorders and CreateBorders) by going to VB Editor and then Insert/Module
and then put the code there and closed it. I can see the module 5 is there
with the macros but when I try to run the sub it jumbs to another module
(Module4) and I can see now that it can not find the macros there. What am I
doing wrong?
Thanks again for your patience Rowan,
Dori

"Rowan Drummond" wrote:


Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:

Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:



Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:


Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:




Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:



Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:





In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:




I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Rowan,
I made the changes but no result. I can see that the worksheet_change event
code is inside "ThisWorkbook" under the VBA Project on the left side of VB
Editor window, but it still jumps to macros in Module4 when I run the
Sub/UserForm (F5).
I entered Application.EnableEvents = True
after hitting (Ctrl+G) and it appeared in a window below the window of
ThisWorkbook. Is that OK?
I hope not driving you crazy!
Thanks for your support.
Dori

"Rowan Drummond" wrote:

Hi Dori

Forget the other macros for now - in fact delete them and remove module
5 completely.

Now right click on sheet1 and select view code. Delete any
worksheet_change event that is currently on that sheet. Then paste this
event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now open the immediate window (Ctrl+G) and type the line:
Application.EnableEvents = True
Then hit enter.
Return to excel and in Sheet1 cell C9 select 1 from the dropdown. Do you
get a message saying CreateBorders?

Regards
Rowan

DORI wrote:
Hi Rowan,
Still not working, I think I am doing something wrong as when I try to run
the sub it does not recognize the macros you gave me. I created the 2 macros
(ClearBorders and CreateBorders) by going to VB Editor and then Insert/Module
and then put the code there and closed it. I can see the module 5 is there
with the macros but when I try to run the sub it jumbs to another module
(Module4) and I can see now that it can not find the macros there. What am I
doing wrong?
Thanks again for your patience Rowan,
Dori

"Rowan Drummond" wrote:


Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:

Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:



Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:


Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:




Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:



Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:





In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:




I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Dori

The worksheet_change event must be in the sheet's code module not the
thisworkbook module. Delete it from the ThisWorkbook module and insert
it into sheet1's module.
You can't run an event using the F5 key. The way to test that this is
working is by changing the value of cell C9. You should then see a
message box saying createborders of ClearBorders depending on the value
you choose in C9.

See also
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://www.cpearson.com/excel/events.htm

Regards
Rowan

DORI wrote:
Hi Rowan,
I made the changes but no result. I can see that the worksheet_change event
code is inside "ThisWorkbook" under the VBA Project on the left side of VB
Editor window, but it still jumps to macros in Module4 when I run the
Sub/UserForm (F5).
I entered Application.EnableEvents = True
after hitting (Ctrl+G) and it appeared in a window below the window of
ThisWorkbook. Is that OK?
I hope not driving you crazy!
Thanks for your support.
Dori

"Rowan Drummond" wrote:


Hi Dori

Forget the other macros for now - in fact delete them and remove module
5 completely.

Now right click on sheet1 and select view code. Delete any
worksheet_change event that is currently on that sheet. Then paste this
event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now open the immediate window (Ctrl+G) and type the line:
Application.EnableEvents = True
Then hit enter.
Return to excel and in Sheet1 cell C9 select 1 from the dropdown. Do you
get a message saying CreateBorders?

Regards
Rowan

DORI wrote:

Hi Rowan,
Still not working, I think I am doing something wrong as when I try to run
the sub it does not recognize the macros you gave me. I created the 2 macros
(ClearBorders and CreateBorders) by going to VB Editor and then Insert/Module
and then put the code there and closed it. I can see the module 5 is there
with the macros but when I try to run the sub it jumbs to another module
(Module4) and I can see now that it can not find the macros there. What am I
doing wrong?
Thanks again for your patience Rowan,
Dori

"Rowan Drummond" wrote:



Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:


Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:




Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:



Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:





Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:




Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?
Thanks,
Dori

"Rowan Drummond" wrote:






In a normal module add the following macro:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Then in the sheet module for sheet1 (right click the sheet tab and
select view code) add the following event code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = True
If Target.Count = 1 Then
If Target.Address = "$C$9" And Target.Value = 2 Then
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
End If
End If
Exit_Event:
Application.EnableEvents = False
End Sub


Hope this helps
Rowan

DORI wrote:





I am wondering if this scenario is possible: operators input values (1 or 2)
in the 'sheet 1'!C9. Then I want to remove the borders in F13:H20 and N15:N17
and E22:E26 on the 'sheet 2' if the 'sheet 1'!C9=2
How can I do that? I have Windows Excel 2000.
Thank you for your help.
Dori

Hi Rowan,
Can I ask you another question?
After I added:
application.screenupdating = false
it works great on my computer (Office XP 2002) but when I opened it on
another computer (Office 2000), when I click on the remove border or create
border macros, it jumps to sheet2, creat or remove borders and then stayes
there. Any idea? How can I fix it as most of users have Office 2000?
Thanks,
Dori

"Rowan Drummond" wrote:

Hi Dori

Glad you got it working. To stop the screen jumping when it runs add
application.screenupdating commands to the change event eg:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
application.screenupdating = false

If Target.Count = 1 Then
'etc

Exit_Event:
Application.EnableEvents = True
application.screenupdating = true
End Sub

Regards
Rowan

DORI wrote:
Hi Rowan,
Thank you so much for being so helpful and patient with me. I deleted the
code from ThisWorkbook and inserted into sheet1 and it worked.
I changed the MsgBx code to Call Macro for removing or creating the borders
and it is working fine. The only thing is that it jumps to sheet2 for a
moment and comes back to sheet 1, which is not a big deal but it would be
much easier to enter data for users without any distraction.
Thanks again,
Dori

"Rowan Drummond" wrote:


Hi Dori

The worksheet_change event must be in the sheet's code module not the
thisworkbook module. Delete it from the ThisWorkbook module and insert
it into sheet1's module.
You can't run an event using the F5 key. The way to test that this is
working is by changing the value of cell C9. You should then see a
message box saying createborders of ClearBorders depending on the value
you choose in C9.

See also
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://www.cpearson.com/excel/events.htm

Regards
Rowan

DORI wrote:

Hi Rowan,
I made the changes but no result. I can see that the worksheet_change event
code is inside "ThisWorkbook" under the VBA Project on the left side of VB
Editor window, but it still jumps to macros in Module4 when I run the
Sub/UserForm (F5).
I entered Application.EnableEvents = True
after hitting (Ctrl+G) and it appeared in a window below the window of
ThisWorkbook. Is that OK?
I hope not driving you crazy!
Thanks for your support.
Dori

"Rowan Drummond" wrote:



Hi Dori

Forget the other macros for now - in fact delete them and remove module
5 completely.

Now right click on sheet1 and select view code. Delete any
worksheet_change event that is currently on that sheet. Then paste this
event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now open the immediate window (Ctrl+G) and type the line:
Application.EnableEvents = True
Then hit enter.
Return to excel and in Sheet1 cell C9 select 1 from the dropdown. Do you
get a message saying CreateBorders?

Regards
Rowan

DORI wrote:


Hi Rowan,
Still not working, I think I am doing something wrong as when I try to run
the sub it does not recognize the macros you gave me. I created the 2 macros
(ClearBorders and CreateBorders) by going to VB Editor and then Insert/Module
and then put the code there and closed it. I can see the module 5 is there
with the macros but when I try to run the sub it jumbs to another module
(Module4) and I can see now that it can not find the macros there. What am I
doing wrong?
Thanks again for your patience Rowan,
Dori

"Rowan Drummond" wrote:




Hi Dori

First lets make sure that the event is working. To do this replace the
change event with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
MsgBox "CreateBorders"
Case 2
MsgBox "ClearBorders"
End Select
End If
End If

Exit_Event:
Application.EnableEvents = True
End Sub

Now if you change the value of C9 to 1 you should get a message saying
CreateBorders, and when you select 2 you should get a message saying
ClearBorders. I tested this with C9 and D9 merged and datavalidation
allowing selection from a list containing 1 and 2 and it worked fine for
me.
If you can get that working then we can replace the message boxes with
the code to create and remove the borders. So for example you would
replace the line:
MsgBox "CreateBorders"
with
Call RemoveBorder

Make sure that your recorded macro refers to the range on sheet2
specifically as any unqualified references will be assumed to refer to
sheet1 as this is where the event code is sitting. So your macro should
work if you select sheet2, select the range and then clear borders.

Hope this helps
Rowan

DORI wrote:



Thanks Rowan, I made the changes but still no results.
I recorded 2 different macros: "RemoveBorder" for removing the borders from
the cells on sheet2 and "InstallBorders" for reinstalling the borders to the
cells on sheet2. I ran both macros and they work just fine.
Now, I just need to know how to activate the 2 Macros automatically when the
'sheet1'C9=1 to activate "RemoveBorder" and to activate "InstallBorder" when
the 'sheet1'C9=2. What kind of code should I use to perform above function?
Thanks again for all your help.
Dori

"Rowan Drummond" wrote:





Hi Dori

Apologies, my original post had some typos as well as some flawed logic.
Hopefully this will correct both. In your new module (I think you said
module 5) replace the ClearBorders macro with these two:

Sub ClearBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).LineStyle = xlNone
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Sub CreateBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
If rng.Columns.Count 1 Then _
.Borders(xlInsideVertical).Weight = xlThin
If rng.Rows.Count 1 Then _
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

Then replace the change event in sheet1's module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exit_Event
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Address = "$C$9" Then
Select Case Target.Value
Case 1
CreateBorders (Sheets("Sheet2").Range("F13:H20"))
CreateBorders (Sheets("Sheet2").Range("N15:N17"))
CreateBorders (Sheets("Sheet2").Range("E22:E26"))
Case 2
ClearBorders (Sheets("Sheet2").Range("F13:H20"))
ClearBorders (Sheets("Sheet2").Range("N15:N17"))
ClearBorders (Sheets("Sheet2").Range("E22:E26"))
End Select
End If
End If
Exit_Event:
Application.EnableEvents = True
End Sub

Finally use the immediate window to enableevents again.
(Application.EnableEvents = True). Then test again.

Regards
Rowan


DORI wrote:




Thanks Rowan for getting back to me.
I enabled the events but still no luck. I want the borders stay on if
someone enters number 1 and to be removed if the number 2 is entered in the
c9. I forgot to mention that the C9 cell has a menu list for numbers 1 or 2
that users can pick one of them. Is that a problem?
I also tried to Run the macro with VBA Editor but it jumbs to another module
that I have macros in. Why?
Thanks,
Dori

"Rowan Drummond" wrote:






Hi Dori

This won't do anything when you type 1 in C9 but it should remove the
borders when you type in 2. Check that events are enabled. To do this
open the immediate window in the VBE (Ctrl+G). Then type:
application.enableevents = true
and hit enter. Go to sheet1 and type 2 in the cell C9. Does it remove
the boders from sheet2 now?

What do you want it to do when you enter 1?

Regards
Rowan

DORI wrote:





Thanks Rowan.
I created a new module (module 5) for the first code and put the second code
in "view code" on sheet 1 tab right click. It seems nothing happens when I
enter 1 or 2 in C9. I unprotected the worksheets but no success. I am new to
VBA/macro, what am I doing wrong?

Hi,

try this, put it in the end of the macro.
Sheets("sheet1").Select

Juuljus

Hi Dori

The fact that it stops on sheet2 makes me think it has probably failed
for some reason. The error handler has caught it which is why you
haven't been prompted that an error occured. If you post the code you
are using as well as the change event that calls the macros to add and
remove borders I may be able to find the problem.

Regards
Rowan

DORI wrote:
Hi Rowan,
Can I ask you another question?
After I added:
application.screenupdating = false
it works great on my computer (Office XP 2002) but when I opened it on
another computer (Office 2000), when I click on the remove border or create
border macros, it jumps to sheet2, creat or remove borders and then stayes
there. Any idea? How can I fix it as most of users have Office 2000?
Thanks,
Dori

"Rowan Drummond" wrote:

Thanks Juuljus,
It worked.
Dori

"Juuljus" wrote:

Hi,

try this, put it in the end of the macro.
Sheets("sheet1").Select

Juuljus

Thanks Rowan for getting back to me, I added:
Sheets("sheet1").Select
at the end of macro (per Juuljus instruction) and it resolved the problem.
Will let you know if the problem comes back.
Thanks again,
Dori

"Rowan Drummond" wrote:

Hi Dori

The fact that it stops on sheet2 makes me think it has probably failed
for some reason. The error handler has caught it which is why you
haven't been prompted that an error occured. If you post the code you
are using as well as the change event that calls the macros to add and
remove borders I may be able to find the problem.

Regards
Rowan

DORI wrote:
Hi Rowan,
Can I ask you another question?
After I added:
application.screenupdating = false
it works great on my computer (Office XP 2002) but when I opened it on
another computer (Office 2000), when I click on the remove border or create
border macros, it jumps to sheet2, creat or remove borders and then stayes
there. Any idea? How can I fix it as most of users have Office 2000?
Thanks,
Dori

"Rowan Drummond" wrote: