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
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,940
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
 

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,940
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
 

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
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

Board Regular
Joined
May 3, 2011
Messages
111
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,940
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

Board Regular
Joined
May 3, 2011
Messages
111
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
 

Forum statistics

Threads
1,082,368
Messages
5,365,042
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top