I hope you can help me as I am stuck at the beginning of my new VBA project and haven't found anything useful on the internet so far.
I am trying to create an excel sheet which varies slightly depending on entries in a userform. The thing I am stuck on is the following: I need to populate a cell with information that is kinda entered into the userform: XXYYZZ - LL
XX being the last two digits of the year (entered as a date into the userform dd.mm.yyyy) YY being data from a combobox (few possibilities) ZZ being some kind of counter (haven't figured out how to count this either as the counter should go up depending on how many data sheets have been produced before with the same XXYY) LL being either numbers or letters entered into a textbox in the userform
Is there any way I could achieve this or is this hopeless? The only way I could have thought about solving this is with
range("A1").value = [yy&combobox1.text&ZZ&" - "&textbox1.text]
Is this possible? Any better ideas? Still have to figure out a way to count.
Something like the following should do it. You will still need to determine how you will populate "ZZ" - in this example I have it just set to 1. My example assumes the value in TextBox1 is in date format, that ComboBox1 only has 2 character values and that the value entered in TextBox2 is always 2 characters. This code would need to be assigned to a command button on your userform:
Private Sub CommandButton1_Click()
Dim counter As Long
counter = 1
ActiveSheet.Range("A1").Value = Right(DatePart("yyyy", TextBox1.Value), 2) & ComboBox1.Value & Format(counter, "00") & " - " & TextBox2.Value
Unload Me
End Sub
There are controls you can implement to ensure the data entered is in the format you require for this code to work, but this should get you started at least.