Can't draw borders for hidden columns

[ShieBoon]

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

 

[dk]

What is the code you're using? I just did this test and it drew the border without problem:

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

 

[ShieBoon]

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.

 

[dk]

Hi

your code worked ok for me when I used this:

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

 

[ShieBoon]

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. :|

 

[ShieBoon]

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...

 

[dk]

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?

 

[ShieBoon]

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