Activecell select properties

[dgold82]

This is a variance on an earlier question of mine. I was trying to completely
hide the little border when selecting a cell. Doesn't look like you can do
this.

I was thinking that perhaps we can change the visible properties of it. I
saw a great addin from someone (can't remember who) that allows for an active
cell to be highlighted certain colors as you click around, but not the actual
select border. If I can make it white or transparent that could be a good
workaround for me.

My end goal was really to make a worksheet unselectable (inputs are radio
buttons) but I need it to be selectable for certain macros and hyperlinks to
work. I am trying to mimic the look of an unselectable worksheet.

Help would be most appreciated!

 

[Steven B]

Hello,

Not sure what your end goal looks like, but it sounds like you are
using the inputs to create some sort of report perhaps.

What if you set up your workbook as multi-sheet
Sheet 1 = inputs
Sheet 2 = hidden data
Sheet "New" = Sheet created by macro to display final report data
generated by macros in Sheet 1

You can still protect the book to prevent user modification, or
unprotect/reprotect the workbook using the macro as well.


Steven

 

[Jim Thomlinson]

http://www.cpearson.com/excel/RowLiner.htm

 

[dgold82]

Thanks so much for the replies!

Jim:

Yes, it was rowliner that I was thinking about above. It actually does
exactly the opposite of what I want to do with my worksheet. There is no
option to remove the black border excel puts around an active cell. I have
been playing with it for a while.

Steven:

My workbook has dozens of worksheets, they are organized like you mentioned.
Input pages (that are radio buttons), hidden pages which all the input pages
dump too and report pages.

Problem is that the input pages and reports need to be clickable/selectable
when locked. If they aren't then many of my hyperlinks don't work and some
other macros as well.

This is not really a deal breaker. One of the ways I was thinking about
getting around it was to place a rectangle over a worksheet and make the fill
100% transparent. Kinda ugly behind the scenes, but it prevents the user from
clicking on cells while allowing them to be selectable while locked.

I would still love a way to hid the select border if possible on an active
cell. I'm sure there is a way to do it via VBA somehow. Thanks for the help
though!

 

[ryguy7272]

I found the code below on this very same DG a while back:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant
v = Array(xlEdgeBottom, xlEdgeTop, xlEdgeRight, xlEdgeLeft)
For Each r In ActiveSheet.UsedRange
With r
For i = 0 To 3
..Borders(v(i)).LineStyle = xlNone
Next
End With
Next

For i = 0 To 3
With ActiveCell.Borders(v(i))
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = 7
End With
Next
End Sub


HTH,
Ryan---

 

[dgold82]

Hi Ryan--thanks much for the attempt.

I have been playing with your code below for quite a while and it does some
interesting things, but not remove the select border on an active cell. And
by the way, I am not talking about a regular cell border that can be easily
defined in the cell format. I want to leave that the same as well as all of
my formatting.

I just want a way to make the select border go away so that my workbook
looks a bit more professional while allowing a cell to be technically
selectable (for many reasons). Starting to lose hope on this endeavor. I have
spent many hours searching to no avail.

Thanks again.