Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing

Cell Formulas
Range	Formula
L5	L5	="Die Bewerbung ist "&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-G5&" Tage her"
L7:L8,L10:L30	L7	=IF(AND([@Status]<>"Zusage",[@Status]<>"Absage",[@[Tage seit Bewerbung]]>21),"Mehr als 3 wochen her. Unwahrscheinliche Antwort","")
K5:K30	K5	=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-G5
D5:D30	D5	=ROW()-ROW(Bewerbungsliste[#Headers])

which is just a simple application list for me. I wanted to create a userform which makes it a lil easier for me to populate a new row.

the problem colums im talking about are "Datum", "Letztes Update", "Tage seit bewerbung" and "Kommentar".

What i wanted to achieve is that when my textbox for the colum "Datum" is empty, then it should add the date of today and if the textbox for "letztes update" is empty then it should leave the cell empty. The row "Tage seit Bewerbung" is just overall a bit ****y and i dont even know what to do here. Also the Column "Kommentar" just doesnt take over the existing formula.

thats how my last row looks like when i enter an empty userform:

Bewerbungsliste.xlsm

D

E

F

G

H

I

J

K

L

31

27

#########

StepStone

12:00:00 AM

30.11.2023

Tabelle1

Cell Formulas
Range	Formula
D31	D31	=ROW()-ROW(Bewerbungsliste[#Headers])
K31	K31	=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-G31

here is the the code for entering the values from the userform:

VBA Code:

Private Sub EnterExit_Click()

Dim Firma As String
Dim Ort As String
Dim dtBewerbung As Date
Dim Status As String
Dim dtLetztesUpdate As Date
Dim Kommentar As String

If Me.dtBewerbung = "" Then
Me.dtBewerbung = Date
End If

If Me.Status = "" Then
Me.Status = "warte auf Antwort"
End If


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim tbl As ListObject
Set tbl = ws.ListObjects("Bewerbungsliste")
Set lrow = tbl.ListRows.Add
With lrow

.Range(2) = Firma
.Range(3) = Ort
.Range(4) = dtBewerbung
If Me.optBewerbungHomepage.Value Then .Range(5) = "Bewerbung per Homepage"
If Me.optBewerbungTelefon.Value Then .Range(5) = "Bewerbung per Telefon"
If Me.optDirekt.Value Then .Range(5) = "Direktbewerbung (E-Mail)"
If Me.optGetInEngineering.Value Then .Range(5) = "Get-In-Engineering"
If Me.optIndeed.Value Then .Range(5) = "Indeed"
If Me.optLinkedIn.Value Then .Range(5) = "LinkedIn"
If Me.optStepStone.Value Then .Range(5) = "StepStone"
If Me.optXing.Value Then .Range(5) = "Xing"
.Range(6) = Status
.Range(7) = dtLetztesUpdate
.Range(9) = Kommentar

End With
'Me.Hide
End Sub

Anthony47 · Dec 1, 2023

Do you have one of the TextBoxes in your userform named dtBewerbung and a variable with the same name? If "Yes" then be aware that the textbox doesn't magically export its content to the variable.
So somewhere you need to execute a dtBewerbung = cDate(Me.dtBewerbung.Value) for having the variable populated.
Of course I think is quite error prone assigning objects (eg a textbox) the same name of a variable; and probably there is even not the need for exporting the textbox value to a variable and then assign the variable to a cell: I mean you can directly assign the textbox value (using a conversion function, eg cDate to convert a "text" to a "Date") to a cell.

I don't see how you set the value for variables Firma, Ort, Status and other, so I suspect that you have textboxes with these same names and you expect that their values magically move to the variable, that is not the way the userform works.

I hope you understand what I mean and revise your project to:
-use variables names that are different from the name of the controls (recommanded)
-add the instructions to move the values of the controls to the variables, or move directly the values from the controls to the table cells

Bye

Xalova · Dec 2, 2023

Anthony47 said:
Do you have one of the TextBoxes in your userform named dtBewerbung and a variable with the same name? If "Yes" then be aware that the textbox doesn't magically export its content to the variable.
So somewhere you need to execute a dtBewerbung = cDate(Me.dtBewerbung.Value) for having the variable populated.
Of course I think is quite error prone assigning objects (eg a textbox) the same name of a variable; and probably there is even not the need for exporting the textbox value to a variable and then assign the variable to a cell: I mean you can directly assign the textbox value (using a conversion function, eg cDate to convert a "text" to a "Date") to a cell.

I don't see how you set the value for variables Firma, Ort, Status and other, so I suspect that you have textboxes with these same names and you expect that their values magically move to the variable, that is not the way the userform works.

I hope you understand what I mean and revise your project to:
-use variables names that are different from the name of the controls (recommanded)
-add the instructions to move the values of the controls to the variables, or move directly the values from the controls to the table cells

Bye

no,i dont have a variable with the same name as a textbox. But what might make you think that this is the case is that i thought that i had to dim the values from the textboxes too...? please correct me here if i am wrong.

otherwise i handled the problem by manually re-formatting the cells that are getting formated with code. not the prettiest solution, but it works

Anthony47 · Dec 2, 2023

no,i dont have a variable with the same name as a textbox

I'm sorry but I have to disagree:
-When you write Dim dtBewerbung As Date you are declaring a vba variable whose name=dtBewerbung and type=Date
-When you write Me.dtBewerbung, aren't you referring to a TextBox (this is my best guess) whose name=dtBewerbung?
So it seem that YOU DO HAVE a variable and a textbox having the same name

i thought that i had to dim the values from the textboxes too

A "textbox" returns a string, you cannot change that nor you can "dim" it
Again, insisting on the previous point, the instruction Dim dtBewerbung declares a vba "variable", and doesn't affect the textbox that is named "dtBewerbung"

Similarly, when you write Dim Firma As String you are declaring a vba variable; then you never assing a value to that variable, so the instruction .Range(2) = Firma will put an empty string into ".Range(2)". My best guess for that strange situation is that you have a textbox named "Firma" and you assume that the content of this textbox magically moves into the variable with the same name; but that is wrong: you have to move the content of the textbox to the variable, then can move the variable to ".Range(2)"; or you can move directly the content of the textbox directly to the cell (for example: .Range(2) = Me.Firma.Value)

This situation happens for several variables, and I guess that for each of those variables there is a textbox having the same name

You can review the names of the controls on the userform by displaying (within the Vba editor) the userform, then selecting each control and inspecting the "Property box" (that shows not only the Name but the other properties that you can manage)

Xalova · Dec 4, 2023

Anthony47 said:
I'm sorry but I have to disagree:
-When you write Dim dtBewerbung As Date you are declaring a vba variable whose name=dtBewerbung and type=Date
-When you write Me.dtBewerbung, aren't you referring to a TextBox (this is my best guess) whose name=dtBewerbung?
So it seem that YOU DO HAVE a variable and a textbox having the same name

A "textbox" returns a string, you cannot change that nor you can "dim" it
Again, insisting on the previous point, the instruction Dim dtBewerbung declares a vba "variable", and doesn't affect the textbox that is named "dtBewerbung"

Similarly, when you write Dim Firma As String you are declaring a vba variable; then you never assing a value to that variable, so the instruction .Range(2) = Firma will put an empty string into ".Range(2)". My best guess for that strange situation is that you have a textbox named "Firma" and you assume that the content of this textbox magically moves into the variable with the same name; but that is wrong: you have to move the content of the textbox to the variable, then can move the variable to ".Range(2)"; or you can move directly the content of the textbox directly to the cell (for example: .Range(2) = Me.Firma.Value)

This situation happens for several variables, and I guess that for each of those variables there is a textbox having the same name

You can review the names of the controls on the userform by displaying (within the Vba editor) the userform, then selecting each control and inspecting the "Property box" (that shows not only the Name but the other properties that you can manage)

Wow, didnt know that. Thank you for making it clear for me!

I have removed the "dim's" and now it works as if nothing happened (i commented out the formatting in the code). Thank you for helping me

Anthony47 · Dec 4, 2023

I have removed the "dim's" and now it works as if nothing happened

Does it work correctly or "nothing happens" [when you press EnterExit]?
If "nothing happens" then publish the code for Sub EnterExit_Click

Xalova · Dec 5, 2023

Anthony47 said:
Does it work correctly or "nothing happens" [when you press EnterExit]?
If "nothing happens" then publish the code for Sub EnterExit_Click

sorry for the misunderstanding.

It does work perfectly fine now and does exactly what i wished it to do

thank you a bunch again

Anthony47 · Dec 5, 2023

Thank you for the feedback
If the problem is solved then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing

Xalova

Board Regular

Excel Facts

Anthony47

Well-known Member

Xalova

Board Regular

Anthony47

Well-known Member

Xalova

Board Regular

Anthony47

Well-known Member

Xalova

Board Regular

Anthony47

Well-known Member

Forum statistics

Share this page

Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock