• 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.

Userform, submit to generate number in column B

Ben Millen

New Member
Hi All, so im creating a maintenance job sheet, each job needs a job number generating in column B when the userform is submitted

im not sure what to put in the submit button code on the userform to do this (very new to vba "like 4 days")

any help/guidance much appreciated
 

sgmpatnaik

Active Member
@Ben Millen

Welcome to Chandoo.org

Paste the below formula in the Column D1 or where ever

=SUM(IF(FREQUENCY(B:B,B:B)>0,))

The Above formula count only unique No. not the duplicate

Now Place this code in UserForm

Code:
Private Sub UserForm_Initialize()
TextBox1.Value = Worksheets("Daily Transaction").Range("D1").Value + 1
TextBox1.Value = Format(TextBox1.Value, "0000")
End Sub
Now you will get the Number in UserForm TextBox1

Hope it give some idea, other wise please upload a sample file

Thanks
 
Hi Ben,

Do you need to auto generate the job number? If so then put the below code in the submit button.

Code:
<Sheet name>.<Range name>= <Required Number>

If you need more help then send me the sample work book I will do it for you.

Regards
Abdul Matheen
 

Ben Millen

New Member
thanks both, sorry im struggling to figure where i put the code either of you suggested

Abdul, yeah i need to auto generate the number, not to worried about where that number comes from just needs to be an identifier for jobs

ive included the workbook
 

Attachments

Ben Millen

New Member
hi i saw that when searching for a solution, but like i say im a noob so not sure where the code needs to go and also they seem to be using hidden cells or separate sheets which i dont want to do if possible.
 

Ben Millen

New Member
Brilliant, Thanks Patnaik, thats pretty much it, dont know if you noticed but there are several other issues on the form

do you by any chance know how to keep a table row hidden when filters are used?
 

sgmpatnaik

Active Member
@Ben

Sorry i didn't check the UserForm Totally just i add a extra line in there that's all and what type of error you are getting in the form and where you will get a Circular Reference Warning, i didn't get any circular reference warning in my pc

I think there is a formula in B1 Kindly remove that and now you didn't get the circular reference warning and i have place the formula in the Column Y1

kindly inform if any issue

Thanks
 
Last edited:
Top