User form dropdown

[Kmahraz]

Hi:
Looking for some assistance with the items below :

Request 1:
Drop down (Distributor won't work )

Request 2:
Based on distributor name selected I would like the Case number to be generated automatically looking something like ((Distributor name- Case number - year)) exp.
Lucky-0001-15.
Bear-0002-15.
Eagle-0003-15
Deer-0004-15.
....
...
.
Request 3:
Case number will be saved in column A.

Request 4:
I would like the data to be saved in sheet (Customer Info), currently it save in the active sheet.

I did some of the coding' just looking for some help and guidance/ suggestions.

Any help will be greatly appreciated.
Please see attachment
Best,

K

 

[Kmahraz]

Hello Ninjas, any assistance with my request please?

 

[vletm]

@Kmahraz
You use some object that I cannot use on that Form.
Could You tell what are those and what are those functions of that form?

 

[Kmahraz]

Hello Vletm,
Not sure what object you referring to; but i will try to provide as much information. Please see picture below and let me know if you need any additional information:

 

[vletm]

@Kmahraz - at least that calendar no work with me, is that also multitabform?

 

[Kmahraz]

@vletm, thank you, i am trying to capture the date of the entry with that calendar but was unable to do so, i believe i will need to do a pop-up.... something like that.
I will really appreciate any help with the other items in the user form.
please let me know if you need any additional information.
Best,
K

 

[vletm]

@Kmahraz - here You are ... only for testing ...
Notes:
Add ... adds to sheet
Next ... hmm?, clears Form, for what?
Clear ... clears Form
Cancel ... hmm? ... closes Form
Moving with Tab ... works
Distributor Info Frame ... works
There were more ... something that I cannot use, no matter so far
* Ideas .... Questions?

 

[Kmahraz]

@vletm
Thank you so so much for the file, it gave me some ideas that i would like to implement in my code.
Attached you will find what i was able to generate... i like the idea of using color coding...

Add function is used to add to sheet
Next will be moving to the next page
Clear.... clear form
Cancel... close form
Distributor info as shown in my file

I was able to add a function to generate the case number automatically.

The only item i am struggle with is the date entry, looking for some ideas

Again a appreciate the help and suggestions!

Best,
K

 

[vletm]

@Kmahraz
That calendar... I found old codes ... like ...
Activesheet.MonthView.MinDate = 'The Earliest Day'
Activesheet.MonthView.MaxDate = 'The Latest Day'
Activesheet.MonthView.Value = 'Selected day'
You can find more easy ...
... and I don't use that object anymore!
> Where do You use that date? So far anywhere!
>>Of course, the case number could be random, but then there could be double case numbers or more. The case number would start from 1 per year per Distributor and it should add by one per case...

 

[Kmahraz]

@vletm , again thank you for the help and suggestion
I was able to find an old calendar, where it will provide the option to select the date...please see below
Any idea on how I would change the case number code to make sure i won't have a duplicate? the idea i had is to use the distributor code selected with the case number and the year, something like PENN-00001-2015.
is this feasible?

Again very grateful for the help!

Best
K

 

[vletm]

@Kmahraz
Because, there isn't date information with 'Customer Info'-sheet,
You could get Distributor's Total Case-number like this
(I modified to my code sample ):

Private Sub cboDept_Change()
    Application.ScreenUpdating = False
    Dim ans As Long
    Dim CN As String
    CN = WorksheetFunction.Text(WorksheetFunction.CountIf(Sheets("Customer Info").Range("J:J"), cboDept.Value) + 1, "0000")
    Application.EnableEvents = False
        TextBox109.Value = CN
    Application.EnableEvents = True
    txtSassociate.Value = cboDept.Value & "-" & TextBox109.Value & "-" & Year(Now) - 2000
    If TextBox109.Value = "Enter Case Number" Then
        txtSassociate.BackColor = &HFF&
        TextBox109.BackColor = &HFF&
        ans = MsgBox("Enter Case Number!", vbCritical, "Missing information!")
        TextBox109.SetFocus
    Else
        txtSassociate.BackColor = &HFFFFFF
    End If
End Sub

Ideas ... Questions?

 

[Kmahraz]

@vletm
This is Great, I will make some slight modification to your code to fit exactly what I need, basically the sale associate will enter their name in that case and the case number box will show LEIPZ-0001-15.

If it's possible do you know of a way to retrieve the data entered if I know the case number?
Example, I created case number LEIPZ-0001-15 and I want to retrieve the customer info and populate everything else?
Thank you so much!

Best Regards,
K

 

[vletm]

@Kmahraz
>Your original case was 'Distributor'-'case number'-'year'.
That works so ... select 'Distributor' and last two part will solve.
>Case number (in my case) is connected with 'Distributor'. There can be many same 'Case numbers'! You gotta use the whole 'set' of 3.
That is possible too. If I got You idea ...?

 

[Kmahraz]

@vletm
Yes you are correct, the case will be 'Distributor'-'case number'-'year', i just need that to display in the case number textbox.

What i am trying to do now is an option to retrieve all the information related to a case entered.

So if I type LEIPZ-0001-15 I would like the information to be displayed as shown below:

 

[vletm]

@Kmahraz
Why do You write it again? You have it ready is the sheet!
If You have a lot of rows, it could filter by seen 'Distributor' ...
Same option should be with as many places as possible...

 

[Kmahraz]

@vletm, the Retrieve option is going to be used in a different page, as you know I will be using multi-page function, and i will have more data, so my intention are to provide an option where the user will be able to retrieve data entered for a previous customer... please let me know if this make sens.

Best,
K

 

[vletm]

@Kmahraz
I don't see just now connection with 'User Info' and 'Case Number'.
There're too many missing things, so I cannot have an idea of You case.
So far this looks like only one sentence from book of 500pages.

 

[Kmahraz]

@vletm,

I apology for the confusion, let me try to explain,
Customer Info: is one of the Pages(Tabs) in the user form please see below.

There are several information and calculation that I will be capturing with this form, if a User decide to retrieve a case number I would like to gave them that option so that they can retrieve all the data and be able to review/ change or add ...and avoid having them retype the entire data ..

As you can see I have several tabs in my user form,

• Customer info,
• Cost data,
• Related cost
• report...

Please let me know if you have any questions,

Best,

Karim

 

[vletm]

@Kmahraz
Are You going to allow more than one user in time to use this workbook; Share?
If so ... You should know how do that works, not only how do it should work.
If not ... then You'll have more freedoms.

 

[Kmahraz]

@vletm,
They will be several users, they won't be using it at the same time and it's going to be used at different locations ...
please let me know if you need any additional information.
Best,
K

 

[vletm]

@Kmahraz
I don't need more information, You got Your 'user from dropdown'.

 

[Kmahraz]

@vletm,
Thank you for the help and feedback!
Best,
K