Userform: Combining 2 values into 1 record

[KDS14589]

I have a problem or situation that I posted on another forum (I will not link to it because I'm still mad) a week ago an 'Experienced Forum Member' gave me a solution which privately I had tried before with no luck. I tried his solution anyway with the outcome I knew was coming. When I replied he advised me that I had not given him all the details, I re-replied WITH EVERYTHING that he asked for. After several days of nothing (I checked his 'Status', active on other posts) I'm trying other forums from now on.

Now my problem…

I have a userform (Data_Entry_UF) that has many text boxes and combo boxes. The first combo box (Combo_Intake_Type) is filled by a dynamic Name Range (controls_intake_types) that refers to


=OFFSET(Controls.General!$D$4,0,0,COUNTA(Controls.General!$D$4:$D$270),1)

, a single column. This column can be accessed by the user to enter the Intake types they wish (at present I have only 'All', 'Geometry', and 'Mechanical' for testing). One text box (txt_record) has the vba code of



Dim sh As Worksheet
Set sh = ShDA01
Dim lr As Long
Dim x As Long


x = ShDA01.Range("C" & Rows.Count).End(xlUp).Row
lr = ShDA01.Range("C" & Rows.Count).End(xlUp).Row

With Txt_Record
.Value = Format(Val(Range("C" & Rows.Count).End(xlUp)) + 1, "00000")
.Enabled = True
End With


Both the Combo_Intake_Type and the Txt_Record work fine. But I'm trying to include the value from Combo_Intake_Type (the first 2 letters from the selection) into the record number. Such as record 00123 and the selection from Combo_Intake_Type is 'Geometry' the record on the userform would display 00123-GE. One of his concerns was if no select is made: the form unloads.

I've tried in the txt_record vba code


.Value =Format(Val(Range("C" & Rows.count).End(xlUp)) + 1, "00000") & "-" & Left(combo_intake_type, 2)

But with no luck (it was his suggestion too).
I'll accept any help or suggestions (beyond me calming down).

[Aussiebear]

I have a problem or situation that I posted on another forum (I will not link to it because I'm still mad)

That's interesting.... but doesn't excuse you from linking the other forum. Please indicate the other forum so those members here who wish to assist can view what has transpired to date.



...a week ago an 'Experienced Forum Member' gave me a solution which privately I had tried before with no luck. I tried his solution anyway with the outcome I knew was coming. When I replied he advised me that I had not given him all the details, I re-replied WITH EVERYTHING that he asked for. After several days of nothing (I checked his 'Status', active on other posts) I'm trying other forums from now on.

Do you understand that those people who contribute to forums, do so both voluntarily and openly sharing of their knowledge? Perhaps the the "other forum member" is busy elsewhere with other people's issues, or possibly the "other forum member" is slightly aggrieved with your latest post. I understand that you may well be impatient for a response, but you need to be patient.



I'll accept any help or suggestions (beyond me calming down).

I reread your post a number of times, and still come up with the image that you are easily agitated. Take a deep breath and calm down. Most people reading this thread will simply walk away without helping you simply because of what you have written.

[snb]

Sub M_snb()
if combo_intake_type.listindex>-1 then Txt_Record = format(application.max(columns(3))+1, "00000_") & Left(combo_intake_type, 2)
End Sub

[KDS14589]

I appreciate the help. But I'm still getting nothing but the number not 00123GE but just 00123, I must be doing something wrong. If I can't get this working I'll just put a code in the worksheet that stores this data and use THAT # for searches and etc. :banghead:

[p45cal]

This code works fine here:

.Value = Format(Val(Range("C" & Rows.Count).End(xlUp)) + 1, "00000") & "-" & Left(combo_intake_type, 2)
29596

If you have not selected anything in the combobox then you get nothing after the hyphen:
29597

Best attach a workbook with this problem.

If you've posted this topic at other sites you do need to supply links - it's the same at 99% of forums of this type.
Why? Have a read of http://www.excelguru.ca/content.php?184
I won't respond to this topic anymore unless you supply link(s).

[KDS14589]

I got it to work, before I had it in the userform Activate procedure. I moved it into the save data procedure. THANKS :clap: