Hide/Unhide sheet gridlines with VBA

Assuming you are running the macro from the form the sheet on which you want to hide the gridlines( so that it is the active sheet), the following will hide them.

ActiveWindow.DisplayGridlines = False

At the end of your macro turn them back on by setting the above to TRUE

Hi Derick,

A little tip - with things like this its always worth trying to use the Macro Recorder while you carry out the action and then examining the code. This would have given you the code that Tom has supplied ;;)

Anyway, to cover the situation where the gridlines are already off you could try something along these lines:

Sub Test()
    Dim bGridOn As Boolean
    
    If ActiveWindow.DisplayGridlines Then
        bGridOn = True
        ActiveWindow.DisplayGridlines = False
    Else
        MsgBox "Gridlines are already off!"
    End If
    '
    MsgBox "Do your stuff"
    '
    If bGridOn Then ActiveWindow.DisplayGridlines = True
    
End Sub

HTH

Quote

Originally posted by Derick
Thanks Tom and Richie. I have it working well. I am now wondering how to do it so if gridlines were off it leaves them off afterward, and if they were on, it puts them on again afterward (ie. return to original state). I don't need a message box, just needs to run automatically.

Thanks again,

Derick :spin:

Derick,

That's what the example routine above actually does ;;) - just remove the messageboxes (they were just for illustration purposes).

EDIT: Sorry, Richie, I didn't read to the end of your code. You were way ahead of me.

You should be able to use a slight modification of Richie's code. I haven't tested it, but try something like the following.

At the start of your macro determine the initial state and store it in a variable:

If ActiveWindow.DisplayGridlines Then 
        bGridOn = True 
      Else 
        bGridOn = False 
End If

At the end of your macro use your stored value to set the value to the initial state.

ActiveWindow.DisplayGridlines = bGridOn

Derick,
I am having trouble opening your .emf files so have not been able to play with them or look at them thus far. But I do have one suggestion if you are working in an MS Office application with your image: Record an macro while you size it in that application. (You can record macro in Powerpoint and Word just like you can in Excel.) Then as part of your macro use the info you record to size and place the image as you want to. Note that while your macro is working inside of Powerpoint, it needs to use Powerpoint macro commands (same if in Word).

A thought. I have not tested this, but if you are using the cells to size the image, then when you turn off the gridlines (and the border thickness goes away) this probably would cause your image to be smaller.

Is the size change dramatic? Or is it in the range of cell border thikness? If the border thickness might be the problem, then try oversizing the cells prior to running the macro. Or, if possible, size the image cells to the correct size while the borders are turned off.