Posts by ysdai7287

    Re: Programmatically add/delete a control in a worksheet

    Quote from royUK;800220

    This example creates a Label which contains unique entries from the column and resizes to the width of the column. I've also added an example for a ListBox.

    Thanks! This is the kind of code I was looking for, "dynamic" stuff (just didn't know how to properly phrase it). I'll just need to figure out how it works and implement the technique in my own code.
    Very cool and useful stuff. Thank you! :)

    Re: Programmatically add/delete a control in a worksheet

    Well, I didn't know you could do that. Thanks.
    But that wasn't exactly the answer I was looking for.
    The combobox was just an example. Could be a spinner for fine-tuning or a simple userform, based on the selected cell. Create and remove controls on the fly, using just the code.


    I have a worksheet with certain cells that, when I double-click on them, will show a combobox containing all items already in the column. I used a dynamic range for that. The code is as follows:

    And I have another code in the SelectChange event to stash away the combobox when not in use, like this:

    This works pretty well as long as I use this code in my computer. If I were to import this code into the same worksheet on another computer, I'd have to manually go into the Developer's tab, insert a combobox somewhere in the sheet, name it "cboCombo" and set its initial properties, or else the code won't work.

    So my questions are:
    1. How do I create this combobox, set its initial properties, and delete after use on-the-fly, using just the VBA code? It's an ActiveX control, not a form control.
    2. How do I check if this combobox already exists in the sheet, so I don't have to add it?

    The ideal solution would be that, I just import this code, and any worksheet with this layout would get this combobox function, without the need to manually add a new control. And if the control is already there, skip the whoe thing.

    Re: VBA code to generate serial numbers in a specific format

    Quote from sktneer;797298

    The latest and max possible order Num will always be in the last row in column C OR it may exist anywhere in column C?
    Don't you have a sequence no. field in your data set? Is there any Date field?

    Can you share a dummy sample workbook with the layout as same as that of your actual workbook to work with?

    Here, I have attached a dummy file that I use to test my code. There is a date field but that's kind of irrelevant because the newest entry may not contain the newest OrderID, just the most recent activity, like delivery of a previous order (they also use this sheet to track deliveries). That was why I had to look for the max serial value and got stuck at the millennium crossover (the millennium bug?).

    Now this is what it looks like at the crossover at the moment:
    Since the max is always 999, the subsequent ones are always 001

    This file is the stripped down version of the actual one they use, which is a lot messier. The serial should reset back to 001 at the cross of the year or 999.

    Re: VBA code to generate serial numbers in a specific format

    My manager came to me and said: don't change the data structure, don't make my staff rearrange data, just come up with a code that can autogenerate the next number based on the existing data...
    That was why I used the MaxValue approach and hit this 999-reset wall...
    I have refined my code a bit but still can't find a way to get past this 999 and resume normal operation...
    Any other approach other than MaxValue? I know I could also set up some kind of counter somewhere in the code, but everything will reset when I restart Excel. I could also hide the counter somewhere in the worksheet, but I was hoping I can achieve this just through VBA code.

    Hi, I'm new to this forum and also quite new in VBA coding.

    I'm trying to write some VBA code to generate a serial number that uses this format: YYMM-000, where YYMM is extracted from the current date (1708, for example), and 000 is the running number.
    In my code I extract the maximum value from the column that contains the serial numbers, increment it and put that new formatted number into the next new row of that column. The reason I used the MaxValue approach is so that I don't have to mess with the user's existing data, just find the newest number and increment it, and for some reason the user's existing data may not be in chronological order.
    It's working pretty well except for a catch: when the number reaches 999, it should reset to 001. Since I look for the MaxValue, this is where I hit the bottleneck.
    Is there a way to fix this? Here's my code. Thanks.