Macro prevents typing in cells, until switching to another tab and back again

[bleepy]

Hello,

Fairly new to Excel/VBA stuff, so forgive me if I've made a stupid mistake somewhere.

I have a spreadsheet formatted like a calendar, and have some simple ActiveX buttons on the right.

When one of these buttons are clicked, it runs the following macro on the currently selected cell:

<code>
Sub ColorCodeA()
  Selection.Interior.ColorIndex = 50
End Sub
</code>

There are many macro's like this, running from A through to K (ColorCodeA, ColorCodeB, etc.)

However, once I have clicked this, I can no longer enter any data in to any cells on that sheet until I have switched to another tab, and back to the original one.

It doesn't matter if the cell/sheet/workbook has any protection settings active or not, and I don't have to do anything else except literally switch to another tab and back again.

I'm not sure what could be causing this. I have some other macro's in the workbook, spread across three module's, but this still happens even if they haven't been run since closing and re-opening the workbook.

I would appreciate any assistance in trying to find the source of this annoying problem.

Possible solutions from other forums I have tried are:

Application.ScreenUpdating = False
<code>Selection.Interior.ColorIndex = 50</code>
Application.ScreenUpdating = True

<code>Selection.Interior.ColorIndex = 50</code>
Sheets("problemsheet").Select
Range("A1").Select

Regards

Craig

 

[Jaafar Tribak]

I personally never experienced problems with ActiveX controls embeded in worksheets but I heard this can be an issue

I suggest you replace the ActiveX buttons with normal excel form buttons which are less bugy or if you must keep the activeX buttons try setting their TakeFocusOn_Click (remove the undersocre)Property to False and see if that solves the problem.

 

[Jon von der Heyden]

I second Jaafar's suggestion to use Forms controls. I had a project once where I used a multi-select listbox ActiveX control and it produced this exact same behaviour.

 

[bleepy]

I personally never experienced problems with ActiveX controls embeded in worksheets but I heard this can be an issue

I suggest you replace the ActiveX buttons with normal excel form buttons which are less bugy or if you must keep the activeX buttons try setting their TakeFocusOn_Click (remove the undersocre)Property to False and see if that solves the problem.

Changing this property fixed it. Working perfectly now, thanks!