Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    ActiveX Combo Box font size keeps changing

    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Sue

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

    zeddy

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Just the combo box.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Smile

    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
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Any chance someone could help me out with my previous post?

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  10. The Following User Says Thank You to zeddy For This Useful Post:

    SueP (2012-03-01)

  11. #10
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •