• ActiveX Combo Box font size keeps changing

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » ActiveX Combo Box font size keeps changing

    Author
    Topic
    WSSueP
    AskWoody Lounger
    February 24, 2012 at 9:06 am #481775

    Hi… I’ve added a combo box to a spreadsheet. Every time I click the box to make a selection the font gets smaller and smaller. I figure there must be a setting that’s wrong. I’ve played around with the size, scale and aspect ratio in format controls but I’m not doing something right.

    Sue

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • zeddy
      AskWoody_MVP
      February 24, 2012 at 10:24 am #1321666

      Hi Sue

      Is it just the combo box or is the whole sheet getting smaller and smaller???

      zeddy

      Reply | Quote
      • WSSueP
        AskWoody Lounger
        February 24, 2012 at 1:12 pm #1321718

        Just the combo box.

        Reply | Quote
        • zeddy
          AskWoody_MVP
          February 24, 2012 at 4:08 pm #1321744

          Is there any VBA code assigned to the combo box?

          Would it be possible to upload a cut-down copy or sample of the file exhibiting this behaviour??

          zeddy

          Reply | Quote
    • WSSueP
      AskWoody Lounger
      February 27, 2012 at 2:19 pm #1322217

      Hi Zeddy… thanks for the response. While this issue frustrated me to no end on Friday, I’m back in the office and the issue seems to have disappeared! Guess I should be thankful.

      However if you could help me something else I’d appreciate it. Here’s what’s going on…

      I’m attaching a copy of my workbook.

      The ‘Input Sheet’ is for staff to enter their expenses. The ‘Acctg Copy’ is the copy that is sent to the accounting dept (they don’t want to see all the detail that the individual depts want). The data sheet provides a ‘database’ so the data for all staff can be pulled into an MSAccess DB.

      The staff enter the data only once in the ‘Input Sheet’. The rest of the data is pulled from the ‘Input Sheet’. I discovered that the data won’t pull from a combo box directly so I used the activeX combo box which allows me to link the data in the box to a cell where I can pull the data from. I’m pretty much there but am having problems with the ‘Details Code Desc’ combo box. I not sure how to get the activeX combo box to display the drop down list based on the content of the Details Code cell (C9).

      Thanks

      Reply | Quote
      • WSSueP
        AskWoody Lounger
        February 29, 2012 at 7:01 am #1322429

        Any chance someone could help me out with my previous post?

        Reply | Quote
        • zeddy
          AskWoody_MVP
          February 29, 2012 at 7:32 am #1322430

          Hi Sue
          Sorry for delay – just spotted I hadn’t replied.

          Re: I discovered that the data won’t pull from a combo box directly

          Solution: don’t use a combobox – use Data-Validation with a dropdown list.
          See attached file. No problem with links to other sheets.

          This means you don’t need activeX controls on the Input worksheet either, which I try to avoid whenever possible.

          So, to ‘fix’ your problem with the Details Code Description, we can forget about activeX and simply use a ‘lookup’ type formula in cell [D9] etc, replicated in additional cells as required.

          I added a range name DetailsDescription for the description cells alongside your existing range named DetailsCode.

          I used the ‘Indent’ cell format for [D9] because the dropdown arrow of cell [C9] ‘gets in the way’.

          zeddy

          Reply | Quote
    • WSSueP
      AskWoody Lounger
      February 29, 2012 at 1:22 pm #1322474

      Hi Zeddy, I think I may have misled you a bit. The ‘Details Code Desc’ field needs to be a drop so the user can choose a description from the data the ‘DropDowns’ worksheet.

      Example: If the user chooses ‘7400’ in C9 then a combo or list box in D9 needs to display the choices in ‘DropDowns’ sheet. Then the selected description in D9 needs to appear in DataSheet!F2.

      There are only choices if the value in column C is 7400, 7210 or 7110. If any of the 8000 numbers are chosen then the ‘Details Code Desc’ is blank.

      Hope this makes things clearer.

      Reply | Quote
      • zeddy
        AskWoody_MVP
        February 29, 2012 at 3:43 pm #1322498

        Hi Sue

        OK, got it.
        See attached file.

        So I changed the cell [D9] from a formula to a ‘dependant’ dropdown.
        The dropdown will now show list options depending on the choice made in dropdown in [C9].
        If no selection has been made in cell [C9], the dropdown in [D9] will be ’empty’ i.e. you have to choose a selection for [C9] first.

        If you make a selection from the first dropdown in [C9], then make a selection from the dropdown in [D9], AND THEN go back and make a DIFFERENT selection from the first dropdown in [C9], THEN the entry in cell [D9] uses Conditional Formatting to show whether the selection in [D9] is valid or not (showing RED background if invalid). User should just delete any cell entry with a RED background and reselect from dropdown.

        This has been copied to other corresponding cells i.e. [D13], [..], [D29]
        Also, I amended the formulas in cells [B12], [B16] etc to NOT show #N/A when the corresponding Details Code cell [C9] etc is empty.

        I rest my case.

        zeddy

        Reply | Quote
        • WSSueP
          AskWoody Lounger
          March 1, 2012 at 6:57 am #1322634

          Thank you so much… Works perfect. I’ve not been much of an Excel user in the past, more Access, but I think I’ll consider using Excel more often. Thanks again!

          Sue

          Reply | Quote
          • zeddy
            AskWoody_MVP
            March 1, 2012 at 9:37 am #1322665

            Hi Sue

            Thank you for your thank you.

            In my previous file I left a formula in cell [H3] on sheet [InputSheet] which is not required. My fault.

            As punishment, I removed your module1 in the file and added my own modPrint to print your expenses report on sheet [Acctg Copy].
            See attached file.

            My simple print code just does a print preview, but you could easily change this to actually print to the default printer if you want.

            I also removed some unused code snippets in the Sheet and ThisWorkbook objects (left-hand explorer panel of the VBA code window) as I can’t stop myself from tidying up.

            zeddy

            Reply | Quote
    Viewing 2 reply threads
    Reply To: ActiveX Combo Box font size keeps changing

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.