Stop Excel Control being Clicked

Textbox hack

In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.

Hiding Multiple Sheets in Excel Using xlVeryHidden

Flexible solution

It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.

VBA Window Split

I just found out you can split the VBA code window. See images below.

Use the small icon above the right side scroll bar.

This can be useful if you have a long block of code and need to look at separate parts together.

Thanks to Bob Umlas the Excel MVP for sharing.

 

You can double click the split bar to remove it.

Related Posts

Outlook macro to stop sending an email without an attachment [VIDEO]

Automatic attachment check via VBA

I don’t do many Outlook macros, but this one is really useful and I have used it for a long time. It looks at your outgoing email and checks to see if you have the word attach in it and then checks to see if you have an attachment. It warns you if you don’t.

Excel Data Validation Blind Spot

Macro to fix it

One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.

Old Macros XL4 macros

If you use the old Excel macro language – known as XL4 macros – you may need to update a setting to keep using them.

This is the macro language before VBA was introduced in the Excel 5 back in the 90’s.

Not many people use these macros any more but there a couple of techniques that they are used for.

Microsoft will soon disable them automatically and you will need to turn them back on if you want to use them.

The setting to update is in the Trust Center Macro settings – see image below.

 

Related Posts

 

 

Keyboard Shortcut to Record a Macro

You need the Developer ribbon tab visible to record a macro, or do you?

This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down.

Alt T M R

You can also use the little icon next to the Ready at the bottom left corner of the screen.

Once you start recording the small square icon to stop recording appears in the same spot.

Related Posts

Page Breaks in Excel

Turn them off

When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them.

It’s a one-line macro that turns off the page breaks in the current sheet.

Sub TurnOffPageBreaks()
 
ActiveSheet.DisplayPageBreaks = False
 
End Sub

If you are new to macros then this blog post can take you through how to use them.

The above code can be copied and pasted into the code window.

If you want to turn page breaks off for all the sheets in the file use the code below.

Sub TurnOffAllPageBreaks()
 
Dim ws
 
For Each ws In Worksheets
 
  ws.DisplayPageBreaks = False
 
Next ws
 
End Sub

 

I hope these code snippets are useful.

Speed up a macro

Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/

He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds.

Worth a try if you have a longer running macro. You can add it to your opening and closing routines.

Code to turn it off

Application.Interactive = False

And then turn it back on at the end of your code.

Application.Interactive = True

Thanks Kevin for sharing.

Related Posts