KDS14589
04-07-2022, 10:23 AM
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).
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).