• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Button That Generates a Number 1 Higher Than Any Previous Within Data Sheet

O

opattison

New Member
Hello,


So what i am looking to do with my userform is to have it so once the userform has been filled out you can click on the 'generate contract number' which will in turn search the database for the previous number and go 1 higher than that. the other part to this is that i would like it to be able to change the number generated dependent on the variable 'Stream' for instance:


If the 'Stream' SW is selected the current number is 6805 so it would need to generate 6806 as the Number when button is clicked, ideally i would have it adding SW prior to the number also.


second example would be if the EWD 'Stream' was selected it would pull 1 higher than the current one (102) and again it would be great to add a tag so that D103 would be the outcome of pressing the button.


at the moment in the userform i have the contract number locked as i would not want to enter a figure manually.


Here is a link to my workbook currently:

http://www.2shared.com/file/nW_UlpwY/thedata.html


Thank you in advance for any help you might be able to give.


(i have a tendency to over-complicate so if you also know a simpler solution that would be great too)


Regards,

Owen
 
Hi Owen ,


Can you check the file here ?


http://speedy.sh/AxA4W/thedata.xlsm


In the Lookup tab , there is a column for the Maximum Stream Number ; can you add data to your Database tab , and see if the Maximum Stream Number reflects the added data ; if it does , then this can be used to increment the stream number on the form.


Narayan
 
Hello Narayan,


I have checked the workings of the maximum stream number and this is functioning well, obviously what I'm trying to end up with is something that does not require manual entry into the database sheet but everything is inputted from the userform 'Form'
 
Hi Owen ,


What I meant was that if the formula for getting the current maximum stream number is working correctly , then within your Form code , all you have to do is to retrieve the corresponding cell value , based on the Stream code , increment it by 1 and put it on the Form. Isn't this what you were looking for in the first place ?


The Stream table is a smaller table that your main Database ; looking up the Current Maximum Number based on the Stream code , should be far simpler than trying to find out the Current Maximum Number from the Database ?


Narayan
 
Yes i believe that is correct,


Would this be possible to add in VBA to the button press and add a text tag such as SW/CW/D/UCL/MW before the number?
 
Perhaps a better method would be for the formula in the look up sheet to add the increment there and it would be possible to add the text tag here if I'm not mistaken?


then on my button click i could add the stream number dependent on the stream selected in the user form?


sorry for the confusion I'm still developing basic VBA Skills.
 
Hi Owen ,


Can you check the file here ?


http://speedy.sh/WFW8m/thedata.xlsm


I have added formulae in column P on the 'Look Up' tab , to get the next contract number ; when you click on the Generate Contract Number button , the code just copies this value into your form field.


Narayan
 
Narayan,


I cannot thank you enough for this, it is exactly what i need and works well with the slight adaption i made, great job.


Best Regards,

Owen
 
You must log in or register to reply here.
Back
Top