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