• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combobox size and list within keep shrinking

Andyk62

New Member
Hi guys
I'm not experienced in writing vba code. I have created an activex combobox to enable me to quickly move to another worksheet in my workbook. I have managed this mostly by following youtube videos lol!
It works perfectly (surprisingly) BUT every time I click into the box the text within it shrinks and also the size of the box changes when I come back to the worksheet with it on.
I am working in Excel 2010 on windows10 64bit.
I have attached the file accordingly and the combobox in question says 'Go To Batch'
I have googled the problem and searched on here to no avail so far and just wondered if someone could help me.
Many thanks in advance
Andy
 

Attachments

p45cal

Well-Known Member
ActiveX controls on a worksheet are notoriously unpredictable. You could try resetting some of its properties each time the sheet is activated, among them:
Code:
Private Sub Worksheet_Activate()
Dim sh As Worksheet
With Me.ComboBox1
  .Clear
  For Each sh In ThisWorkbook.Worksheets
    .AddItem sh.Name
  Next sh
  .Top = 70
  .Left = 690
  .Height = 30
  .FontSize = 11
  .Width = 230
End With
End Sub
 
Top