Can't draw borders for hidden columns

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
Hi all, I have a database and a few hidden columns and right now i'm using vba to insert new records via a userform. Whenever a new record is inserted via the userform i use codes to draw the borders. However, the hidden columns doesn't have the borders drawn when i unhide the columns.

Any idea how i can access these hidden columns without unhiding them?

Thanks,
Shie Boon

excel 2003
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
What is the code you're using? I just did this test and it drew the border without problem:

Code:
Sub Testing()

Worksheets.Add

Range("G:M").EntireColumn.Hidden = True

Range("H5:L10").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium

Range("G:M").EntireColumn.Hidden = False

End Sub
 
Upvote 0
Cells(NextRecord, 1).Select
ActiveCell.Resize(, FinalCol).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = 1
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With

Here is the code i am using to apply the borders.
 
Upvote 0
Hi

your code worked ok for me when I used this:


Code:
Sub testing2()

'Cells(NextRecord, 1).Select
'ActiveCell.Resize(, FinalCol).Select

    Range("H5:L10").Select


    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = 1
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

End Sub

Do you get any borders drawn at all? What is the selection address when you run the code? BTW, your code will run more quickly if you avoid selecting the range and work directly with the range object (like in my first post) :-)

DK
 
Upvote 0
I see. Thanks for the tip DK. Yes the visible cells get borders drawn around them. But not the hidden ones. I tried it on another workbook too, and it worked. But just not on my current one. Weird. :|
 
Upvote 0
I just checked my selection address in the Immediate window.
This is the selected address,$A$307:$AG$307, which is true. FinalCol is supposed to be 33. And AG is at the 33rd column. hm...
 
Upvote 0
Strange - I can't see a reason why they wouldn't be drawn just because the columns are hidden. Is it possible that they have actually been drawn but there is some formatting in place that is making them invisible e.g. same foreground and background colour?
 
Upvote 0
I don't think so. I used the immediate window to check the borders drawn and what i get is.

Column 29 to 32 are the hidden columns.
?worksheets("MasterList").cells(nextrecord,32).borders(xledgebottom).linestyle
-4142

However if i test for column 28
it will be
?worksheets("MasterList").cells(nextrecord,28).borders(xledgebottom).linestyle
1
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top