VBA code help

[GDF1983]

Hello

I am coming back to a work project, most of what I programmed is working nicely for them, I have added custom ribbons to their excel to make it easier but there is something I would love to try to make work.

That is the following

When you enter the data for individual it will amend their row in the spreadsheet

So user #1 for example if we put in Obs 06/02/2022 then Last Obs in spreadsheet for them would mirror that

What I would like to do is have VB then auto populate the 6th month and 12 months section with the dates so in that is case 06/08/2022 and 06/02/2023 would fill the form and then populate the spreadsheet, can this be done?

Kind Regards

 

[EFANYoutube]

Hi GDF1983
This can be done on your submit button.
range.find("your username/ID/whatever")
Then use .offset to write the control.text into the cell

If you need more help I will need to know what the names of your textboxes are, the name of the work sheet, the column your holding the username/id/whatever to uniquly identify them and where in your form you hold the username...you get it.

then I could write a sudo code for you

 

[GDF1983]

Hi GDF1983
This can be done on your submit button.
range.find("your username/ID/whatever")
Then use .offset to write the control.text into the cell

If you need more help I will need to know what the names of your textboxes are, the name of the work sheet, the column your holding the username/id/whatever to uniquly identify them and where in your form you hold the username...you get it.

then I could write a sudo code for you

Sheets("DO").Range("DO_Start").Offset(TargetRow, 4).Value = Txt_Last
Sheets("DO").Range("DO_Start").Offset(TargetRow, 5).Value = Txt_six
Sheets("DO").Range("DO_Start").Offset(TargetRow, 6).Value = Txt_twelve

Currently is set in the boxes to fill in the Spreadsheet when you manually enter

 

[EFANYoutube]

So does the code you put here work for your purpose? Looks like it should.

 

[GDF1983]

So does the code you put here work for your purpose? Looks like it should.

Yes that code allows for manual inputting

What I want to know is if you can add something that code to auto populate the 6 month and 12 months from the information you place in the Last Obs section.

 

[EFANYoutube]

You'll just need to explain automatically a bit more. You mean find the right row to update?

 

[GDF1983]

Enter value in Last Obs and without typing into 6 month and 12 month it will fill those in based on what value you placed in last obs

eg 06/02/2022 in last obs then 6months will fill out what date 6 months time and like wise 12 months without having to do anymore inputting

 

[EFANYoutube]

Oh that's easy enough
Txt_six.text = datevalue(Txt_last.Text) + 181
Txt_twelve.text = datevalue(Txt_last.Text) + 365
Untested but put this into the txt_last_afterupdate() event
You can make those 2 txt boxes enabled = False after to stop anyone changing the dates also if you want

 

[GDF1983]

So to be clear this how the full code will look after adding the new lines to auto complete the 6 and 12 month entry

Private Sub Submit_Click()
'When Submit Button is clicked

Dim TargetRow As Integer 'variable for position
Dim Name As String 'Name

If Sheets("Engine1").Range("B4").Value = "New" Then

TargetRow = Sheets("Engine1").Range("B3").Value

Else

TargetRow = Sheets("Engine1").Range("B5").Value



End If

Name = Txt_Name


Sheets("DO").Range("DO_Start").Offset(TargetRow, 1).Value = Txt_Name
Sheets("DO").Range("DO_Start").Offset(TargetRow, 2).Value = Txt_Pay
Sheets("DO").Range("DO_Start").Offset(TargetRow, 3).Value = Combo_Depot
Sheets("DO").Range("DO_Start").Offset(TargetRow, 4).Value = Txt_Last
Sheets("DO").Range("DO_Start").Offset(TargetRow, 5).Value = Txt_six.text = datevalue(Txt_last.Text) + 181
Sheets("DO").Range("DO_Start").Offset(TargetRow, 6).Value = Txt_twelve.text = datevalue(Txt_last.Text) + 365

Unload Do_Data_UF

MsgBox Name & " Was either Created or Edited", 0, "Form Completed"
End Sub

 

[EFANYoutube]

Hmmm. Depends if you want the user to see those dates before they click submit. If you don't then just copy straight to the Excel sheet. If you do. Put the code to update the 6 and 12 month txts on txy_last_afterupdate