Userform - textbox value format as a time

[Mysecka]

Hello :)

I made work schedule. There Is sheet wit schedule and sheet with data, constants etc. There is a number of cells that must be populated by a User. I'm trying to make it "user-proof" so I made Button with form initializing. This form contains numerous textboxes that must be populated on start based on cell value. When data is changed it should instantly update cell.

The best i learn is to figure out myself but VBA is too much. I need help on taste it then i will be on my own :)

I figure out that setting ControlSource in properties is enough (at least for now, i know there are more inteligent solutions - give me a break :) ). It populates textbox and change cell. But boxes with time (hh:mm:ss) are populated in decimal... Cells are formated properly.

I tried this:


Private Sub UserForm_Initialize()
TextBox1.Value = Format(TextBox1.Value, "HH:MM")
End Sub

[BigDawg15]

Mysecka,

Try:


Private Sub UserForm_Initialize()
TextBox1.Value = Format(Range("A1").value, "HH:MM")
End Sub

Change the range value to whatever cell you have picking up the info.

Regards,

BigDawg15

[Mysecka]

So now it is:



Private Sub form_Initialize()
tboGodzina1a.Value = Format(Range("Dane!E2").Value, "HH:MM")
tboGodzina2a.Value = Format(Range("Dane!E3").Value, "HH:MM")
tboGodzina3a.Value = Format(Range("Dane!E4").Value, "HH:MM")
tboGodzina4a.Value = Format(Range("Dane!E5").Value, "HH:MM")
tboGodzina5a.Value = Format(Range("Dane!E6").Value, "HH:MM")
tboGodzina6a.Value = Format(Range("Dane!E7").Value, "HH:MM")
tboGodzina7a.Value = Format(Range("Dane!E8").Value, "HH:MM")
tboGodzina8a.Value = Format(Range("Dane!E9").Value, "HH:MM")
End Sub


Where "tboGodzina1a" etc. are my boxes with given time and information about value is in "E2" cell on Sheet "Dane" etc.

Still don't work :( I guess i must set initial value of box according to given cell value (next textbox is formated properly but has no effect on cell). No shortpass... Then what happen when I change the data in text box etc.

Could You give me some hint how should I code every step?

[BigDawg15]

Upload a sample of your workbook with any sensitive information removed, and I will
look further.

BigDawg15

[BigDawg15]

If I understand what you want try this instead of your above code:


Private Sub Userform_Activate()
tboGodzina1a.Value = Format(Range("Dane!E2").Value, "HH:MM")
tboGodzina2a.Value = Format(Range("Dane!E3").Value, "HH:MM")
tboGodzina3a.Value = Format(Range("Dane!E4").Value, "HH:MM")
tboGodzina4a.Value = Format(Range("Dane!E5").Value, "HH:MM")
tboGodzina5a.Value = Format(Range("Dane!E6").Value, "HH:MM")
tboGodzina6a.Value = Format(Range("Dane!E7").Value, "HH:MM")
tboGodzina7a.Value = Format(Range("Dane!E8").Value, "HH:MM")
tboGodzina8a.Value = Format(Range("Dane!E9").Value, "HH:MM")
End Sub


Otherwise you will need to upload a small sample of workbook.

BigDawg15

[SamT]

Try "hh:mm" or "h:mm" or "h:mm am/pm"

also try

Sheets("Dane").Range("E2").Value = TimeValue(tboGodzina1a.Value)


Thanks to: http://exceldesignsolutions.com/> Time Characters

CHARACTER

REMARK


h
Displays the hour as a number 0-23 and does not display insignificant zero’s.


hh
Displays the hour as a number 00-23 and always displays it as a 2-digit number.


[h] or [hh]
Displays the cumulative hours elapsed. The time value could for instance exceed 24 hours (1 day).


m
Displays the minutes as a number 0-59 and does not display insignificant zero’s. Note that this character only represents minutes if used within a complete time number format code.


mm
Displays the minutes as a number 00-59 and always displays it as a 2-digit number. Note that this character only represents minutes if used within a complete time number format code.


[m] or [mm]
Displays the cumulative minutes elapsed. The time value could for instance exceed 60 minutes.


s
Displays the seconds as a number 0-59 and does not display insignificant zero’s.


ss
Displays the seconds as a number 00-59 and always displays it as a 2-digit number.


[s] or [ss]
Displays the cumulative seconds elapsed. The time value could for instance exceed 60 seconds.


am/pm
Displays hours based on the 12-hour clock and indicates morning/afternoon with AM/PM. Note, this is case-sensitive.

[BigDawg15]

Thanks for that info Sam. I had never seen that before.

Good explanation of time characters.

BigDawg15

[SamT]

Bookmark that link, they explain much more about formatting than just the time characters.

[snb]

So in this case use:


msgbox formatdatetime(cells(5,1),4)

[Mysecka]

I tried to upload file, i can't post url. I can sent it via personal message.

[Mysecka]

Omg :) I thought if I change UserForm name to "form" I need use it like names of TextBox :) Now it works fine.

Now I wonder (becouse i have this TextBoxes like 60 to populate, change and update) - can I speed up things a bit? Data in cells are like this:

14222

On left textbox is assigned as "tboGodzina1a" and on right "tboGodzina1b". First column is for worh shift number.

[SamT]

Omg :) I thought if I change UserForm name to "form" I need use it like names of TextBox :) Now it works fine.

Now I wonder (becouse i have this TextBoxes like 60 to populate, change and update) - can I speed up things a bit? Data in cells are like this:

14222

On left textbox is assigned as "tboGodzina1a" and on right "tboGodzina1b". First column is for worh shift number.

I don't understand that table. It has 5 columns, you write about 3 columns.

We have a minimum post count before you can upload. I think it is 5. You have 4 posts at this time.

[Mysecka]

Now my code look like this:



Private Sub UserForm_Initialize()
tboGodzina1a.Value = Format(Sheets("Dane").Range("E2").Value, "HH:MM")
tboGodzina2a.Value = Format(Sheets("Dane").Range("E3").Value, "HH:MM")
tboGodzina3a.Value = Format(Sheets("Dane").Range("E4").Value, "HH:MM")
tboGodzina4a.Value = Format(Sheets("Dane").Range("E5").Value, "HH:MM")
tboGodzina5a.Value = Format(Sheets("Dane").Range("E6").Value, "HH:MM")
tboGodzina6a.Value = Format(Sheets("Dane").Range("E7").Value, "HH:MM")
tboGodzina7a.Value = Format(Sheets("Dane").Range("E8").Value, "HH:MM")
tboGodzina8a.Value = Format(Sheets("Dane").Range("E9").Value, "HH:MM")
tboGodzina1b.Value = Format(Sheets("Dane").Range("F2").Value, "HH:MM")
tboGodzina2b.Value = Format(Sheets("Dane").Range("F3").Value, "HH:MM")
tboGodzina3b.Value = Format(Sheets("Dane").Range("F4").Value, "HH:MM")
tboGodzina4b.Value = Format(Sheets("Dane").Range("F5").Value, "HH:MM")
tboGodzina5b.Value = Format(Sheets("Dane").Range("F6").Value, "HH:MM")
tboGodzina6b.Value = Format(Sheets("Dane").Range("F7").Value, "HH:MM")
tboGodzina7b.Value = Format(Sheets("Dane").Range("F8").Value, "HH:MM")
tboGodzina8b.Value = Format(Sheets("Dane").Range("F9").Value, "HH:MM")
End Sub


Private Sub CmdZamknij_Click()
Sheets("Dane").Range("E2").Value = tboGodzina1a.Value
Sheets("Dane").Range("E3").Value = tboGodzina2a.Value
Sheets("Dane").Range("E4").Value = tboGodzina3a.Value
Sheets("Dane").Range("E5").Value = tboGodzina4a.Value
Sheets("Dane").Range("E6").Value = tboGodzina5a.Value
Sheets("Dane").Range("E7").Value = tboGodzina6a.Value
Sheets("Dane").Range("E8").Value = tboGodzina7a.Value
Sheets("Dane").Range("E9").Value = tboGodzina8a.Value
Sheets("Dane").Range("F2").Value = tboGodzina1b.Value
Sheets("Dane").Range("F3").Value = tboGodzina2b.Value
Sheets("Dane").Range("F4").Value = tboGodzina3b.Value
Sheets("Dane").Range("F5").Value = tboGodzina4b.Value
Sheets("Dane").Range("F6").Value = tboGodzina5b.Value
Sheets("Dane").Range("F7").Value = tboGodzina6b.Value
Sheets("Dane").Range("F8").Value = tboGodzina7b.Value
Sheets("Dane").Range("F9").Value = tboGodzina8b.Value
Unload Me
End Sub

But I'd like to update cells on change so I would prefer for every box:



Private Sub tboGodzina1a_Change()
Sheets("Dane").Range("E2").Value = tboGodzina1a.Value
End Sub

Private Sub tboGodzina2a_Change()
Sheets("Dane").Range("E3").Value = tboGodzina2a.Value
End Sub

etc.



Code above would be sufficient but... I'm inquisitive person so I'm looking for other solutions :) Step by step.

[snb]

Private Sub UserForm_Initialize()
sn=Sheets("Dane").Range("E2:F9")

for j=1 to ubound(sn)
me("tboGodzina" & j & "a")=sn(j,1)
me("tboGodzina" & j & "b")=sn(j,2)
next
End Sub

[SamT]

In the VBA Editor (VBE), press F$ to insure the Properties Window is open.

Click on each tboGodzina and Set the Tag Property to the Range Address of its Range on Sheets("Dane").

Example: tboGodzina1a.Tag = "E2"

Private Sub UserForm_Initialize()
Dim Ctrl As Control
With Sheets("Dane")
For Each Ctrl In Me.Controls
If Left(Ctrl.Name, 9) = "tboGodzina" Then _
Ctrl.Value = Format(.Range(Ctrl.Tag).Value, "hh:mm")
End If
Next Ctrl
End With
End Sub

[Mysecka]

Thanks! I see it's much shorter. ​I must think it through to understand it. Then I can use it for other boxes as well.

My code is now:





Private Sub UserForm_Initialize()
' TAB 1 - Dane podstawowe
tboKierownik.Value = Sheets("Dane").Range("C2").Value
cboDział.Value = Sheets("Dane").Range("C4").Value
cboData.Value = Format(Sheets("Dane").Range("C6").Value, "yyyy-mm-dd")
tboDniwolne.Value = Sheets("Dane").Range("C8").Value
tboDnipraca.Value = Sheets("Dane").Range("C10").Value
' TAB 2 - Nazwiska pracowników
tboPracownik1.Value = Sheets("Dane").Range("B3").Value
tboPracownik2.Value = Sheets("Dane").Range("B4").Value
tboPracownik3.Value = Sheets("Dane").Range("B5").Value
tboPracownik4.Value = Sheets("Dane").Range("B6").Value
tboPracownik5.Value = Sheets("Dane").Range("B7").Value
tboPracownik6.Value = Sheets("Dane").Range("B8").Value
tboPracownik7.Value = Sheets("Dane").Range("B9").Value
tboPracownik8.Value = Sheets("Dane").Range("B10").Value
tboPracownik9.Value = Sheets("Dane").Range("B11").Value
tboPracownik10.Value = Sheets("Dane").Range("B12").Value
tboPracownik11.Value = Sheets("Dane").Range("B13").Value
tboPracownik12.Value = Sheets("Dane").Range("B14").Value
tboPracownik13.Value = Sheets("Dane").Range("B15").Value
tboPracownik14.Value = Sheets("Dane").Range("B16").Value
tboPracownik15.Value = Sheets("Dane").Range("B17").Value
tboPracownik16.Value = Sheets("Dane").Range("B18").Value
tboPracownik17.Value = Sheets("Dane").Range("B19").Value
tboPracownik18.Value = Sheets("Dane").Range("B20").Value
tboPracownik19.Value = Sheets("Dane").Range("B21").Value
tboPracownik20.Value = Sheets("Dane").Range("B22").Value
tboPracownik21.Value = Sheets("Dane").Range("B23").Value
tboPracownik22.Value = Sheets("Dane").Range("B24").Value
tboPracownik23.Value = Sheets("Dane").Range("B25").Value
tboPracownik24.Value = Sheets("Dane").Range("B26").Value
' TAB 3 - Godziny rozpoczęcia i zakończenia zmian
tboGodzina1a.Value = Format(Sheets("Dane").Range("E2").Value, "HH:MM")
tboGodzina1b.Value = Format(Sheets("Dane").Range("F2").Value, "HH:MM")
tboGodzina2a.Value = Format(Sheets("Dane").Range("E3").Value, "HH:MM")
tboGodzina2b.Value = Format(Sheets("Dane").Range("F3").Value, "HH:MM")
tboGodzina3a.Value = Format(Sheets("Dane").Range("E4").Value, "HH:MM")
tboGodzina3b.Value = Format(Sheets("Dane").Range("F4").Value, "HH:MM")
tboGodzina4a.Value = Format(Sheets("Dane").Range("E5").Value, "HH:MM")
tboGodzina4b.Value = Format(Sheets("Dane").Range("F5").Value, "HH:MM")
tboGodzina5a.Value = Format(Sheets("Dane").Range("E6").Value, "HH:MM")
tboGodzina5b.Value = Format(Sheets("Dane").Range("F6").Value, "HH:MM")
tboGodzina6a.Value = Format(Sheets("Dane").Range("E7").Value, "HH:MM")
tboGodzina6b.Value = Format(Sheets("Dane").Range("F7").Value, "HH:MM")
tboGodzina7a.Value = Format(Sheets("Dane").Range("E8").Value, "HH:MM")
tboGodzina7b.Value = Format(Sheets("Dane").Range("F8").Value, "HH:MM")
tboGodzina8a.Value = Format(Sheets("Dane").Range("E9").Value, "HH:MM")
tboGodzina8b.Value = Format(Sheets("Dane").Range("F9").Value, "HH:MM")
tboGodzina9a.Value = Format(Sheets("Dane").Range("E10").Value, "HH:MM")
tboGodzina9b.Value = Format(Sheets("Dane").Range("F10").Value, "HH:MM")
tboGodzina10a.Value = Format(Sheets("Dane").Range("E11").Value, "HH:MM")
tboGodzina10b.Value = Format(Sheets("Dane").Range("F11").Value, "HH:MM")
tboGodzina11a.Value = Format(Sheets("Dane").Range("E12").Value, "HH:MM")
tboGodzina11b.Value = Format(Sheets("Dane").Range("F12").Value, "HH:MM")
tboGodzina12a.Value = Format(Sheets("Dane").Range("E13").Value, "HH:MM")
tboGodzina12b.Value = Format(Sheets("Dane").Range("F13").Value, "HH:MM")
tboGodzina13a.Value = Format(Sheets("Dane").Range("E14").Value, "HH:MM")
tboGodzina13b.Value = Format(Sheets("Dane").Range("F14").Value, "HH:MM")
tboGodzina14a.Value = Format(Sheets("Dane").Range("E15").Value, "HH:MM")
tboGodzina14b.Value = Format(Sheets("Dane").Range("F15").Value, "HH:MM")
tboGodzina15a.Value = Format(Sheets("Dane").Range("E16").Value, "HH:MM")
tboGodzina15b.Value = Format(Sheets("Dane").Range("F16").Value, "HH:MM")
tboGodzina16a.Value = Format(Sheets("Dane").Range("E17").Value, "HH:MM")
tboGodzina16b.Value = Format(Sheets("Dane").Range("F17").Value, "HH:MM")
tboGodzina17a.Value = Format(Sheets("Dane").Range("E18").Value, "HH:MM")
tboGodzina17b.Value = Format(Sheets("Dane").Range("F18").Value, "HH:MM")
tboGodzina18a.Value = Format(Sheets("Dane").Range("E19").Value, "HH:MM")
tboGodzina18b.Value = Format(Sheets("Dane").Range("F19").Value, "HH:MM")
tboGodzina19a.Value = Format(Sheets("Dane").Range("E20").Value, "HH:MM")
tboGodzina19b.Value = Format(Sheets("Dane").Range("F20").Value, "HH:MM")
tboGodzina20a.Value = Format(Sheets("Dane").Range("E21").Value, "HH:MM")
tboGodzina20b.Value = Format(Sheets("Dane").Range("F21").Value, "HH:MM")
tboGodzina21a.Value = Format(Sheets("Dane").Range("E22").Value, "HH:MM")
tboGodzina21b.Value = Format(Sheets("Dane").Range("F22").Value, "HH:MM")
tboGodzina22a.Value = Format(Sheets("Dane").Range("E23").Value, "HH:MM")
tboGodzina22b.Value = Format(Sheets("Dane").Range("F23").Value, "HH:MM")
tboGodzina23a.Value = Format(Sheets("Dane").Range("E24").Value, "HH:MM")
tboGodzina23b.Value = Format(Sheets("Dane").Range("F24").Value, "HH:MM")
tboGodzina24a.Value = Format(Sheets("Dane").Range("E25").Value, "HH:MM")
tboGodzina24b.Value = Format(Sheets("Dane").Range("F25").Value, "HH:MM")
End Sub


'WPROWADZANIE DANYCH


Private Sub tboPracownik1_Change()
Sheets("Dane").Range("B3").Value = tboPracownik1.Value
End Sub


Private Sub tboPracownik2_Change()
Sheets("Dane").Range("B4").Value = tboPracownik2.Value
End Sub


Private Sub tboPracownik3_Change()
Sheets("Dane").Range("B5").Value = tboPracownik3.Value
End Sub


Private Sub tboPracownik4_Change()
Sheets("Dane").Range("B6").Value = tboPracownik4.Value
End Sub


Private Sub tboPracownik5_Change()
Sheets("Dane").Range("B7").Value = tboPracownik5.Value
End Sub


Private Sub tboPracownik6_Change()
Sheets("Dane").Range("B8").Value = tboPracownik6.Value
End Sub


Private Sub tboPracownik7_Change()
Sheets("Dane").Range("B9").Value = tboPracownik7.Value
End Sub


Private Sub tboPracownik8_Change()
Sheets("Dane").Range("B10").Value = tboPracownik8.Value
End Sub


Private Sub tboPracownik9_Change()
Sheets("Dane").Range("B11").Value = tboPracownik9.Value
End Sub


Private Sub tboPracownik10_Change()
Sheets("Dane").Range("B12").Value = tboPracownik10.Value
End Sub


Private Sub tboPracownik11_Change()
Sheets("Dane").Range("B13").Value = tboPracownik11.Value
End Sub


Private Sub tboPracownik12_Change()
Sheets("Dane").Range("B14").Value = tboPracownik12.Value
End Sub


Private Sub tboPracownik13_Change()
Sheets("Dane").Range("B15").Value = tboPracownik13.Value
End Sub


Private Sub tboPracownik14_Change()
Sheets("Dane").Range("B16").Value = tboPracownik14.Value
End Sub


Private Sub tboPracownik15_Change()
Sheets("Dane").Range("B17").Value = tboPracownik15.Value
End Sub


Private Sub tboPracownik16_Change()
Sheets("Dane").Range("B18").Value = tboPracownik16.Value
End Sub


Private Sub tboPracownik17_Change()
Sheets("Dane").Range("B19").Value = tboPracownik17.Value
End Sub


Private Sub tboPracownik18_Change()
Sheets("Dane").Range("B20").Value = tboPracownik18.Value
End Sub


Private Sub tboPracownik19_Change()
Sheets("Dane").Range("B21").Value = tboPracownik19.Value
End Sub


Private Sub tboPracownik20_Change()
Sheets("Dane").Range("B22").Value = tboPracownik20.Value
End Sub


Private Sub tboPracownik21_Change()
Sheets("Dane").Range("B23").Value = tboPracownik21.Value
End Sub


Private Sub tboPracownik22_Change()
Sheets("Dane").Range("B24").Value = tboPracownik22.Value
End Sub


Private Sub tboPracownik23_Change()
Sheets("Dane").Range("B25").Value = tboPracownik23.Value
End Sub


Private Sub tboPracownik24_Change()
Sheets("Dane").Range("B26").Value = tboPracownik24.Value
End Sub


Private Sub tboKierownik_Change()
Sheets("Dane").Range("C2").Value = tboKierownik.Value
End Sub


Private Sub cboDział_Change()
Sheets("Dane").Range("C4").Value = cboDział.Value
End Sub


Private Sub cboData_Change()
Sheets("Dane").Range("C6").Value = cboData.Value
cboData.Value = Format(Sheets("Dane").Range("C6").Value, "yyyy-mm-dd")
End Sub


Private Sub tboDniwolne_Change()
Sheets("Dane").Range("C8").Value = tboDniwolne.Value
End Sub


Private Sub tboDnipraca_Change()
Sheets("Dane").Range("C10").Value = tboDnipraca.Value
End Sub


Private Sub tboGodzina1a_Change()
Sheets("Dane").Range("E2").Value = tboGodzina1a.Value
End Sub


Private Sub tboGodzina1b_Change()
Sheets("Dane").Range("F2").Value = tboGodzina1b.Value
End Sub


Private Sub tboGodzina2a_Change()
Sheets("Dane").Range("E3").Value = tboGodzina2a.Value
End Sub


Private Sub tboGodzina2b_Change()
Sheets("Dane").Range("F3").Value = tboGodzina2b.Value
End Sub


Private Sub tboGodzina3a_Change()
Sheets("Dane").Range("E4").Value = tboGodzina3a.Value
End Sub


Private Sub tboGodzina3b_Change()
Sheets("Dane").Range("F4").Value = tboGodzina3b.Value
End Sub


Private Sub tboGodzina4a_Change()
Sheets("Dane").Range("E5").Value = tboGodzina4a.Value
End Sub


Private Sub tboGodzina4b_Change()
Sheets("Dane").Range("F5").Value = tboGodzina4b.Value
End Sub


Private Sub tboGodzina5a_Change()
Sheets("Dane").Range("E6").Value = tboGodzina5a.Value
End Sub


Private Sub tboGodzina5b_Change()
Sheets("Dane").Range("F6").Value = tboGodzina5b.Value
End Sub


Private Sub tboGodzina6a_Change()
Sheets("Dane").Range("E7").Value = tboGodzina6a.Value
End Sub


Private Sub tboGodzina6b_Change()
Sheets("Dane").Range("F7").Value = tboGodzina6b.Value
End Sub


Private Sub tboGodzina7a_Change()
Sheets("Dane").Range("E8").Value = tboGodzina7a.Value
End Sub


Private Sub tboGodzina7b_Change()
Sheets("Dane").Range("F8").Value = tboGodzina7b.Value
End Sub


Private Sub tboGodzina8a_Change()
Sheets("Dane").Range("E9").Value = tboGodzina8a.Value
End Sub


Private Sub tboGodzina8b_Change()
Sheets("Dane").Range("F9").Value = tboGodzina8b.Value
End Sub


Private Sub tboGodzina9a_Change()
Sheets("Dane").Range("E10").Value = tboGodzina9a.Value
End Sub


Private Sub tboGodzina9b_Change()
Sheets("Dane").Range("F10").Value = tboGodzina9b.Value
End Sub


Private Sub tboGodzina10a_Change()
Sheets("Dane").Range("E11").Value = tboGodzina10a.Value
End Sub


Private Sub tboGodzina10b_Change()
Sheets("Dane").Range("F11").Value = tboGodzina10b.Value
End Sub


Private Sub tboGodzina11a_Change()
Sheets("Dane").Range("E12").Value = tboGodzina11a.Value
End Sub


Private Sub tboGodzina11b_Change()
Sheets("Dane").Range("F12").Value = tboGodzina11b.Value
End Sub


Private Sub tboGodzina12a_Change()
Sheets("Dane").Range("E13").Value = tboGodzina12a.Value
End Sub


Private Sub tboGodzina12b_Change()
Sheets("Dane").Range("F13").Value = tboGodzina12b.Value
End Sub


Private Sub tboGodzina13a_Change()
Sheets("Dane").Range("E14").Value = tboGodzina13a.Value
End Sub


Private Sub tboGodzina13b_Change()
Sheets("Dane").Range("F14").Value = tboGodzina13b.Value
End Sub


Private Sub tboGodzina14a_Change()
Sheets("Dane").Range("E15").Value = tboGodzina14a.Value
End Sub


Private Sub tboGodzina14b_Change()
Sheets("Dane").Range("F15").Value = tboGodzina14b.Value
End Sub


Private Sub tboGodzina15a_Change()
Sheets("Dane").Range("E16").Value = tboGodzina15a.Value
End Sub


Private Sub tboGodzina15b_Change()
Sheets("Dane").Range("F16").Value = tboGodzina15b.Value
End Sub


Private Sub tboGodzina16a_Change()
Sheets("Dane").Range("E17").Value = tboGodzina16a.Value
End Sub


Private Sub tboGodzina16b_Change()
Sheets("Dane").Range("F17").Value = tboGodzina16b.Value
End Sub


Private Sub tboGodzina17a_Change()
Sheets("Dane").Range("E18").Value = tboGodzina17a.Value
End Sub


Private Sub tboGodzina17b_Change()
Sheets("Dane").Range("F18").Value = tboGodzina17b.Value
End Sub


Private Sub tboGodzina18a_Change()
Sheets("Dane").Range("E19").Value = tboGodzina18a.Value
End Sub


Private Sub tboGodzina18b_Change()
Sheets("Dane").Range("F19").Value = tboGodzina18b.Value
End Sub


Private Sub tboGodzina19a_Change()
Sheets("Dane").Range("E20").Value = tboGodzina19a.Value
End Sub


Private Sub tboGodzina19b_Change()
Sheets("Dane").Range("F20").Value = tboGodzina19b.Value
End Sub


Private Sub tboGodzina20a_Change()
Sheets("Dane").Range("E21").Value = tboGodzina20a.Value
End Sub


Private Sub tboGodzina20b_Change()
Sheets("Dane").Range("F21").Value = tboGodzina20b.Value
End Sub


Private Sub tboGodzina21a_Change()
Sheets("Dane").Range("E22").Value = tboGodzina21a.Value
End Sub


Private Sub tboGodzina21b_Change()
Sheets("Dane").Range("F22").Value = tboGodzina21b.Value
End Sub


Private Sub tboGodzina22a_Change()
Sheets("Dane").Range("E23").Value = tboGodzina22a.Value
End Sub


Private Sub tboGodzina22b_Change()
Sheets("Dane").Range("F23").Value = tboGodzina22b.Value
End Sub


Private Sub tboGodzina23a_Change()
Sheets("Dane").Range("E24").Value = tboGodzina23a.Value
End Sub


Private Sub tboGodzina23b_Change()
Sheets("Dane").Range("F24").Value = tboGodzina23b.Value
End Sub


Private Sub tboGodzina24a_Change()
Sheets("Dane").Range("E25").Value = tboGodzina24a.Value
End Sub


Private Sub tboGodzina24b_Change()
Sheets("Dane").Range("F25").Value = tboGodzina24b.Value
End Sub


'PRZYCISKI FUNKCYJNE


Private Sub CmdZamknij_Click()
Unload Me
End Sub

[Mysecka]

Now i changed my mind (as woman often do :) ) and i need two more columns on sheet "Dane". Problem is that almost every textbox data position has been changed... So i need to Change all code... I would implement SamT solution but I what if i want to change again? In this case i think snb solution will work better.

Snb could You explain me your solution more?

Thanx everyone! There was a moment i thought I can't do this at all...

[SamT]

Now i changed my mind (as woman often do :) ) and i need two more columns on sheet "Dane".
That is why I always design my data sheets and columns before I start working on a UserForm.

Since all work in Sub UserForm_Initialize is on sheets("DANE") you can speed up that code by removing the phrase Sheets("DANE") from all lines and putting With Sheets("DANE") at the top of the sub, and putting End With at the end of the sub. Be sure to leave the dot before Range in each line. Pressing CTRL+H will bring up the Search and Replace dialog.

snb writes great code that is very fast. I like to think that I write good code that is clear and understandable.

When you write code your are designing your Project for two viewpoints, the User, who sees Labels and Values, and the coder who sees Names and Value Locations.

With Columnized data, I often use Named Ranges and name the UserForm Controls after the Range they use

tboGodzina1a.Value = Format(Sheets("Dane").Range("E2").Value, "HH:MM")
tboGodzina1b.Value = Format(Sheets("Dane").Range("F2").Value, "HH:MM")
In your Project, I would name Range("E2:E50") GodzinaA and Range ("F2:F50") GodzinaB. The depth of the Range is irrelevant as long as it is deep enough to allow adding more Values and Controls.

Once you Name a Range, you can Cut And Paste that (entire) Range anywhere and you can; Insert or Delete Columns before that Column; or Rows above the Named Range. Just remember that the Range goes down to Row 50, or whatever bottom row you chose when Naming it. Inserting Cells or Rows inside the Named Range will extend the Range downwards. Deleting them will shorten the Range. You can rearrange the order of the cells with Cut and Insert.

Then I would rename tboGondzina1a to tboGondzinaA1 and tboGondzina1b to tboGondzinaB1. The name Pattern is "tbo"+RangeName+Row number. Row number is the Row of the Named Range, in this case Row 2 of the Sheet is Row 1 of the Named Range . The next pair of controls would be named tboGondzina + (A or B) + 2

The Initializing code, minus the formatting, for the Gondzina Controls is now

Dim Ctrl As Control

With Sheets("DANE")
For Each Ctrl In Me.Controls
If Mid(Ctrl.Name, 4, 8) = "Gondzina" Then _
Ctrl.Value = .Range(Mid(Ctrl.Name, 4, 9)).Cells(Right(Ctrl.Name, 1))
Next Ctrl
End With
And the Assignment code is nearly identical, only swapped around the = sign

Dim Ctrl As Control

With Sheets("DANE")
For Each Ctrl In Me.Controls
If Mid(Ctrl.Name, 4, 8) = "Gondzina" Then _
.Range(Mid(Ctrl.Name, 4, 9)).Cells(Right(Ctrl.Name, 1)) = Ctrl.Value
Next Ctrl
End With
The code for the Pracownik Controls is identical, with the following exceptions: You don't have to rename the Controls and the Named Range "Pracownik" is ("B3:B50")

[mikerickson]

Could I suggest that you make the Tag property of each TextBox the Address of its matching cell.

With Sheets("Dane")
tboPracownik1.Tag = .Range("B3").Address(,,,True)
tboPracownik2.Tag = .Range("B4").Address(,,,True)
tboPracownik3.Tag = .Range("B5").Address(,,,True)
'...
End With
Then latter you can loop to read from the sheet

Dim oneControl As MSForms.Control
If TypeName(oneControl) = "TextBox" And oneControl.Tag <> vbNullString Then
oneControl.Text = Format(Range(oneControl.Tag).Value, "hh:mm")
End With

And run your event code through a single sub.

Private Sub WriteTextBox(aTextBox as MSForms.TextBox)
Range(aTextBox.Tag).Value = aTextBox.Text
End Sub

Private Sub tboGodzina21a_Change()
Call WriteTextBox(tboGodzina21a)
End Sub
Private Sub tboGodzina21b_Change()
Call WriteTextBox(tboGodzina21b)
End Sub
' etc.

If you change the box/cell assignment, only one piece of code has to be changed.

[SamT]

And if she moves the columns around? Again.

[mikerickson]

With the .Address in the .Tag, changing one's mind requires that only one line of code be changed (per box).
It also makes the correspondence a property of the TextBox object, making it easier to code if a ClassModule is used for the event code.

I do like the idea of named ranges (and cut/paste to move things around)


ThisWorkbook.Names.Add(Name:= "myName" & "TextBox1", _
RefersTo:= "=" & ThisWorkbook.Sheets("Data").Range("A1").Address(,,,True)).Visible = False

' uf to sheet
With TextBox1
.Range("myName" & .Name).Value = .Text
End With

' sheet to uf
With someCell
UserForm1.Controls(Mid(.Name, 7)).Text = .Value
End With

' in bulk
For Each oneName in ThisWorkbook.Names
With oneName
If .Name Like "myName*" Then
.RefersToRange.Value = UserForm1.Controls(Mid(.Name, 7)).Text
' or
UserForm1.Controls(Mid(.Name, 7)).Text = .RefersToRange.Value
End IF
End With
Next

[SamT]

Mike,

I'm going to be driving? to Stockton, probably in October. Any chance for a meet and Greet over coffee?