cell properties sniffer?

J

jayd4wg

Board Regular
Joined
Mar 25, 2005
Messages
197
has anyone ever written a cell properties sniffer?

I'm trying to do a simple text and background color inverter, and when i go to white background with black text, i lose the fine gray outline. But later on, i'll be doing much more with it and it would be very helpful to know what the VBA properties are for certain cells.
 

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.
Sort by date Sort by votes
Interesting question. Worth a bit of distraction I think. I'll get back to you if I find something
 
Upvote 0
i'm envisioning a msgBox containing the properties of a clicked cell. THAT would be ideal ;)
 
Upvote 0
jayd4wg,

can you use this

Code: 
Sub color_inverter()
'Erik Van Geit
'050607

Dim CC As Long
Dim FC As Long
Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Selection
CC = cell.Interior.Color
FC = cell.Font.Color
cell.Interior.Color = FC
cell.Font.Color = CC
Next cell

Application.ScreenUpdating = False
End Sub

to display properties
Code: 
For Each cell In Selection
msg = "Borders.Color" & vbTab & cell.Borders.Color & Chr(10) & _
"CurrentRegion" & vbTab & cell.CurrentRegion.Address(0, 0) & Chr(10) & _
"Formula" & vbTab & vbTab & cell.Formula
MsgBox msg, 64, "cell" & cell.Address(0, 0)
Next cell

typing cell and then a dot will give you a list of properties and instructions ... choose what you want

kind regards,
Erik
 
Upvote 0
I there a way to loop thru all of the properties of a cell and list their states?
 
Upvote 0
would be intresting ...

it exists for workbooks
something like this
http://www.mrexcel.com/board2/viewtopic.php?t=111162

Code: 
Sub shProps() 
Dim itemN, myItem, myList 

On Error Resume Next 
For Each p In ActiveWorkbook.BuiltinDocumentProperties 
itemN = p.Name 
myItem = p.Value 
myList = myList & vbCr & itemN & ": " & myItem 
Next 
MsgBox myList 
End Sub

kind regards,
Erik
 
Upvote 0
chead5 said:
I there a way to loop thru all of the properties of a cell and list their states?
Click to expand...

This is how I came upon the correct colorIndex for the sheet I was working on:


Code: 
Sub cellOutlines()
'Written by Jason Basham
'06-07-05
'Description - This cycles thru all available color index numbers, and 
'places an outline around the corresponding rows in column A
'ie. a1 is ColorIndex=1 and a28 is ColorIndex=28

Dim i As Integer
i = 1

    Do While i < 57'loop 57 times, the maximum ColorIndex value
            
            ActiveCell.Borders.LineStyle = xlContinuous
            ActiveCell.Borders.Weight = xlThin ' set to xlThick, will be easier to see
            ActiveCell.Borders.ColorIndex = i
            ActiveCell.Offset(1, 0).Select 'go to next cell down
            i = i + 1
    Loop

 End Sub
 
Upvote 0
jayd4wg,

I think chead5's question was meant to display all kind of properties...
Any takers for that part ?

kind regards,
Erik
 
Upvote 0
erik.van.geit said:
jayd4wg,

I think chead5's question was meant to display all kind of properties...
Any takers for that part ?

kind regards,
Erik
Click to expand...

I was hoping to start some wheels turning in his head with what I posted. but then I thought...a single spreadsheet with all different properties might be kind of tedious...much more so than a sniffer to check the properties of an individual cell IMO. I'm not up to the task, at least not right now, my plate is FULL.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA to change color of Forms Control Button
Replies
6
Views
488
skillilea
S
U
  • Solved
Transfer data but not rows with a certain blank cell
Replies
2
Views
249
UTB
U
D
  • Question
"Disabling" calculation of certain formulas until source cells updated -- is there a better way to do this?
Replies
0
Views
455
d0rian
D
N
  • Question
Duplicate cell to new cell using BOLDTEXT or Arial Black, font color, cell color, etc... as variables.
Replies
2
Views
333
Nvchicky
N
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
823
ajjava
ajjava

Forum statistics

Threads
1,214,786
Messages
6,121,553
Members
449,038
Latest member
Guest1337

Share this page

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