VBA code help request - Handling multiple labels backcolor

  • Hello dear all,

    Its been a privilege to post my query / problem seeking your support/help/hint.


    I am designing and trying to develop an Excel+VBA based program for my dairy milk business, enabling the user to enter daily shift wise milk produced / fetched under respective buffalor/cow (numbered).

    The objective is to ease twice daily milking activity and avoid errors/ mistakes under manual method and improve / speed up recording, summarizing and reporting.



    Excel xlsm file attached (please enable macros).



    FLOW:



    1) Once excel file is opened, it will show date, shift and shed/barn selection screen

    2) Next is detail or activity entry screen. User has to click on respective animal quantity box for which value to be entered.

    3) Once clicked, that respective quantity box will turn to green (for highlight purpose only) and a new screen will appear to enter the milk quantity value

    4) Once a valid quantity value is entered and added, it will be shown under respective animal's quantity box on detail or activity entry screen





    CURRENTLY FACING PROBLEM:



    a) How to identify which quantity box is currently green, so that after entering a valid value it color should be changed from green to normal (like all other).

    Turning all label boxes 1 by 1 is very hectic and resource consuming way.. What could be a smart and efficient way?



    Kindly share thoughts / hints.





    Excel xlsm file attached (please enable macros).

  • Are you referring to TextBoxes on your UserForm?


    Your UserForms have DatePickers. This is not a good ide because yhese controls are not available on all computers.


    Here's some alternatives that should work on every version of Excel.


    Alternative DatePickers.

  • Hello,


    Thanks for your response and suggestion. This tool is not for distribution but only for our internal use, so no worries.


    1) They are labels not textboxes


    I am trying to "Test-Trial" different methods to overcome my problem.


    Please see attached xlsm file.


    Tried Label1.backcolor = n with Variable.backcolor = n" but it gives error "Invalid qualifier"