| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to remove borders in a range of cells?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove borders in a range of cells?
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Borders for range | Excel Discussion (Misc queries) | |||
| how do you remove borders | Excel Discussion (Misc queries) | |||
| Borders nor underline commands remove an unusual underline. ?? | Excel Discussion (Misc queries) | |||
| Remove blank cells from a range | Excel Discussion (Misc queries) | |||
| Remove all borders from a range | Excel Programming | |||
Hybrid Mode
