Activex Controls Changing in Size

[mcarter01]

I am using the command button, option button and checkbox activex controls in a workbook. I have set the font type and size in the properties, set the format control lock aspect ratio on and object positioning to Don't move or size with cells.

With this, the activex controls will randomly get smaller or bigger each time you click on them for the action to occur. The action in the code occurs fine, but the text becomes unreadable either because it is very small or too large to see in the size of the control space. When I switch to design mode and resize the control the text returns to the defined size, but this is not a clear option for usability. Any suggestions on how to resolve?

Thanks!

 

[GlennUK]

What version of Excel? Can you list the code you have for the objects too? I will try to recreate the problem on my machine.

 

[mcarter01]

I am using Excel 2007. Here is an example of one of the controls with this issue:

They are 2 radio controls, that are associated. When one is selected the other is turned off/false. The code is to re-format cells associated with that radio button to shade/make so users do not enter data there.

Thanks for looking into this!

Private Sub Per_Click()
'Logic created to shade secion where data entry is not required
'sets view to not show the actions of the code, simply the results
Application.ScreenUpdating = False

If (Per.Value = True) Then
'selects range and formats change in cell and font color
Range("P29:P37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Range("Q29:Q37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
End If

'set zoom selection
Range("P29").Select

'sets view to show actions again
Application.ScreenUpdating = True
End Sub


Private Sub Num_Click()
'Logic created to shade secion where data entry is not required

'sets view to not show the actions of the code, simply the results
Application.ScreenUpdating = False

If (Num.Value = True) Then
'selects range and formats change in cell and font color
Range("P29:P37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
Range("Q29:Q37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
End If
'set zoom selection
Range("Q29").Select

'sets view to show actions again
Application.ScreenUpdating = True
End Sub

 

[GlennUK]

OK, I'll start experimenting right now. I used to work with an M Carter many years ago.

 

[GlennUK]

I'm not getting anything weird happening here, it all works like it's supposed to. Do you have SP1 applied?

 

[mcarter01]

Yes I have applied SP1. Do you know if I need to make sure that the controls are sized within a cell?

I did work in London, but my last name wasn't Carter back then.

 

[GlennUK]

I think I need to see this on my machine. I'll send you my email address in a PM, and we can go from there.

 

[mcarter01]

Hi Glenn,

Unfortunately there is a large amount of confidential information in my spreadsheet and can't forward on. Thank you for offering to provide additional assistance.

Milissa

 

[GlennUK]

Well, sorry to hear that. I've recreated your objects, and used your code, and it doesn't do what you are seeing .... I'm afraid I'm out of ideas.

 

[rconverse]

I am using the command button, option button and checkbox activex controls in a workbook. I have set the font type and size in the properties, set the format control lock aspect ratio on and object positioning to Don't move or size with cells.

With this, the activex controls will randomly get smaller or bigger each time you click on them for the action to occur. The action in the code occurs fine, but the text becomes unreadable either because it is very small or too large to see in the size of the control space. When I switch to design mode and resize the control the text returns to the defined size, but this is not a clear option for usability. Any suggestions on how to resolve?

Thanks!

I am having the exact same issue with a combobox and listbox.