Converting Form to Active X Combo Box

  • Hello.
    I have inherited a workbook which in the 1st sheet is an the input selections for the other sheets.


    Currently many of the selections require the user to selection items from a combo box (form control) and it all works well, but when printed the font in the box's is too small. I had a look to see if I could change the font size and it would appear that you can only change the font size on an Active X combo box.
    That seemed simple enough so I started changing the boxes over... However the form control boxes outputs a number not the selection, the active X box results is the selection... IE If you select Black from a list of colours and black is 3rd in the list the form control box reports 3 in the desired cell active x reports black.


    All I wanted was to make the text in the selection boxes legible when printed, but I'm going to have to rework the whole workbook unless there is a better way or I can fix the output of the active x combo box


    Thanks in advance if anyone can help
    Gavin

  • Hello and Welcome to the Forum :smile:


    Could you attach a sample file ... with your expected result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello and Welcome to the Forum :smile:


    Could you attach a sample file ... with your expected result ...


    =IF(B7=0,0,CHOOSE(choices!D110,Costs!M52,Costs!M53,Costs!M54))


    Thanks Carim been using the forums for a while to solve my problems but couldn't find an answer to this one


    Not sure what you want me to upload.
    I either need to be able to format the control box I have so the text can be read when printed currently its such a small font compared to the text not in the boxes


    Or switch the output of another selection tool to output the number from the list rather than text

  • Hello,


    Based on the Title you have selected ...


    With your sample file ... I was planning to test ... if ...


    with a macro ... your Form ComboBox could be converted into an ActiveX ComboBox ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • The easiest way would be to replace the controls with Data Validation Lists in the cells. Much better for printing than trying to print controls


    Thanks Roy,
    So if I create data validation tables will it still need to be a macro enabled spreadsheet or can I go back to a regular xlsx file?


    I guess I use a vlookup based on the result to get a number and get the rest of the workbook working

  • With all your Data Validation Lists stored in a separate sheet ... you are ' back to ' a regular ' macro-free ' file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)


  • Thanks Carim.


    Thanks for the offer but I know can convert them, my issue is they don't result in the same way. Is the a way of changing the output on an active x box from a result to a number or is there a better way

  • Quote

    a way of changing the output on an active x box from a result to a number


    If I understand your question :


    You can test CLng() or CDbl() ...


    P.S. Still wondering about .... The TITLE you have selected for your thread ...:roll:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You need to use the ListIndex property of the ActiveX ComboBox, not the Value property

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks Roy,
    So if I create data validation tables will it still need to be a macro enabled spreadsheet or can I go back to a regular xlsx file?


    I guess I use a vlookup based on the result to get a number and get the rest of the workbook working


    You no longer need macros if you follow my suggestion