input data from vba userform textbox to a spreadsheet cell depending on the time/date

[vhookup]

This has boggled my mind for the last few weeks, research hasn't turned up an answer for me as yet.


I have a User form with a set of textboxes to input data to every hour (form has a combobox with the hours on it which the user selects). This data then should be sent to a sheet into respective cells under column headings (z1, z2 and so on) and into the corresponding rows according to the time and date. User form has a label which displays current date, I have tried to get the code to read the current date, then select the time in the combobox and place data into the cells.


I am currently getting an Object Required Error. on the date label. have I defined this wrong?


When I change the code and remove "As Date" the code fills all the cells with the actual text "TextBox193"etc in the entire column respective to the range that was specified, am I missing a command/piece of code here to set the destination more specifically?

Private Sub Commandbutton1_Click()
    Application.ScreenUpdating = False
    Dim formDate As Date
    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim z1 As Range
    Dim z2 As Range
    Dim z3 As Range
    Dim z4 As Range
    Dim z5 As Range
    Dim z6G As Range
    Dim z6F As Range
    Dim z7 As Range
    Dim z8 As Range
    'Destination sheet
    Set ws = Sheet59
    'Label on userform displaying current date
        Set formDate = Label35
    'Label on userform diplaying a set time on the half hour (if within a certain range)
        Set formTime = ComboBox1
    'Range Columns
    Set z1 = Range("E:E")
    Set z2 = Range("G:G")
    Set z3 = Range("I:I")
    Set z4 = Range("K:K")
    Set z5 = Range("M:M")
    Set z6G = Range("O:O")
    Set z6F = Range("Q:Q")
    Set z7 = Range("S:S")
    Set z8 = Range("U:U")


    z1.Value = "TextBox193.Text"
    z2.Value = "TextBox194.Text"
    z3.Value = "TextBox195.Text"
    z4.Value = "TextBox196.Text"
    z5.Value = "TextBox197.Text"
    z6G.Value = "TextBox198.Text"
    z6F.Value = "TextBox199.Text"
    z7.Value = "TextBox200.Text"
    z8.Value = "TextBox201.Text"
    
    Dim x As Integer
    For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
    Next x
    Cancel = MsgBox("Data Input Successful")


    Me.Hide


    Application.ScreenUpdating = True
    End Sub

Thanks for taking the time to read this.

[Dave]

Set formDate = Userform1.Label35.Text

or maybe...

Set formDate = Userform1.Label35.Value

Change Userform1 name to suit. HTH. Dave

[vhookup]

I have modified the code to rectify some errors but now I need to be able to get the code to be sent to specific cells, for example when the form reads the system date and the user selects a time from the combo box the data is pasted to the corresponding row.

Each day in my destination sheet has 17 rows (for the time) So the code should search through column C for the date, then search through column D for the matching time that corresponds to the date in col C. I've scratched my head that much with this....

Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False
    Dim formDate
    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim z1 As Range
    Dim z2 As Range
    Dim z3 As Range
    Dim z4 As Range
    Dim z5 As Range
    Dim z6G As Range
    Dim z6F As Range
    Dim z7 As Range
    Dim z8 As Range
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
        Set formDate = Label35
'Label on userform diplaying a set time on the half hour (if within a certain range)
        Set formTime = ComboBox1
'Range Columns
    Set z1 = Range("E:E")
    Set z2 = Range("G:G")
    Set z3 = Range("I:I")
    Set z4 = Range("K:K")
    Set z5 = Range("M:M")
    Set z6G = Range("O:O")
    Set z6F = Range("Q:Q")
    Set z7 = Range("S:S")
    Set z8 = Range("U:U")


    z1.Value = TextBox193.Text
    z2.Value = TextBox194.Text
    z3.Value = TextBox195.Text
    z4.Value = TextBox196.Text
    z5.Value = TextBox197.Text
    z6G.Value = TextBox198.Text
    z6F.Value = TextBox199.Text
    z7.Value = TextBox200.Text
    z8.Value = TextBox201.Text
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

[vhookup]

Set formDate = Userform1.Label35.Text

or maybe...

Set formDate = Userform1.Label35.Value

Change Userform1 name to suit. HTH. Dave

Thanks fo rthe response Dave

Hi I tried both and I got

"Compile Error: Method or data member not found"

[Dave]

Maybe should be...

formDate = Userform1.Label35.Value

Your code doesn't seem to do any of the stuff you outlined? Why set your variables to a range and then re-set them to a textbox value? Dave

[vhookup]

Maybe should be...

formDate = Userform1.Label35.Value

Your code doesn't seem to do any of the stuff you outlined? Why set your variables to a range and then re-set them to a textbox value? Dave

Because I'm new to the world of VBA, I'm just trying to learn by doing and learning comes with its failures! Obviously this is a failure. I've googled questions and come across similar problems faced by other people but not anyone looking to do the exact same thing as myself so i'm adapting codes and trying to understand why certain actions cause certain reactions (I discovered a thread on a forum with a very similar project, the person who answered posted a set of steps to follow for the person needing help, they outlined certain actions they needed the code to fulfill to get the desired outcome, so i followed as best as I could to that guideline, researching different tutorials about variables and ranges implementing the objects from my own data capture form-which is why my code might look odd to you). I'm also trying to discover what is actually possible and what VBA can't do. Certain terminology i'm unfamiliar with and I want to understand how and why it works.

I would also argue that my code does do a large chunk of what I am trying to do, in that it sends the data to the sheet in the correct columns (yes i know this is probably basic but i'm trying all avenues). I'm experimenting with how to tell the code to be sent to a specific row and this is a stumbling block, which is why i'm here for help, Previously it worked fine on a "lastRow" basis but it wasn't fool proof. I've looked for weeks on the same topic and am not getting anywhere. I've gone round in circles and if i can ask questions to the right people and someone says just one small thing (such as you need to look at X for that and it should work) that sparks the route to a solution I'm very happy, i'm not here to be spoon fed or asking for someone to write me code I'm just after a little pointing in the right direction.

So if data is added to a textbox is that not considered a variable? I'm not a very visual person and words on a page (such as the internet) don't help me much, real world examples videos pictures work best with me so i may not have picked up on the exact meaning of certain items in tutorials I've read.

[vhookup]

This code works if i wanted to put data in row 2, I've always thought it needed something to replace the row number to say if this then that, else...etc

Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False
    Dim formDate
    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
 
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
        Set formDate = UserForm1.Label35
'Label on userform diplaying a set time on the half hour (if within a certain range)
        Set formTime = UserForm1.ComboBox1
'Range Columns
    Range("E2").Value = TextBox193.Text
    Range("G2").Value = TextBox194.Text
    Range("I2").Value = TextBox195.Text
    Range("K2").Value = TextBox196.Text
    Range("M2").Value = TextBox197.Text
    Range("O2").Value = TextBox198.Text
    Range("Q2").Value = TextBox199.Text
    Range("S2").Value = TextBox200.Text
    Range("U2").Value = TextBox201.Text
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

[Toubkal]

Hi,

If I understood, you want to increment the row number each time you click the button.
if so, you just need to find the last used row to write after.

Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False
    Dim formDate
    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
 
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
        Set formDate = UserForm1.Label35
'Label on userform diplaying a set time on the half hour (if within a certain range)
        Set formTime = UserForm1.ComboBox1
'Find the lastRow to write after. 
    LastRow = ws.Range("E" & Rows.Count).End(xlUp).Row + 1           '<---------------------- Added this
'Range Columns
    Range("E" & LastRow).Value = TextBox193.Text 
    Range("G" & LastRow).Value = TextBox194.Text
    Range("I" & LastRow).Value = TextBox195.Text
    Range("K" & LastRow).Value = TextBox196.Text
    Range("M" & LastRow).Value = TextBox197.Text
    Range("O" & LastRow).Value = TextBox198.Text
    Range("Q" & LastRow).Value = TextBox199.Text
    Range("S" & LastRow).Value = TextBox200.Text
    Range("U" & LastRow).Value = TextBox201.Text
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

Good Luck

[vhookup]

Hi,

Good Luck

Hello,

Thanks for the reply, I previously used the lastrow which was ok, but it wasn't secure enough that user duplication was eliminated. I'm working toward a solution that will eliminate any mistakes.

A brief history of the use/purpose of this project should help everyone understand;

Previously it was up to me to input data on a daily basis because no one else understood what they needed to do and were scared they would mess up formula etc, even though sheets were protected (this wasn't practical as I ended up still working whilst i was on paternity leave).
So I decided a that a single userform which loaded up using a .bat file every time a user logged in to a computer which was iron clad would be the route to go down.

Issues arise in the workplace when a lack of communication and different users logging into the same PC coincide. Because of the dynamic nature of the job this would mean data could be duplicated (with last row) if colleagues didn't convey that they had input the data, they would have no access to see the destination spreadsheet (for security of the data, to make them feel more comfortable entering the data) but this meant they could all enter the info without each other knowing, this input would ruin the reports, however, if data was missed I could go in and input where applicable which was more desirable than weeding out the duplicates.

So my thought was use the date and time on the userform as identifiers, these would isolate that specific row for data entry, if more than one user attempts to fill the data for that time period again it will be overwritten with the same data, which again is still more desirable than duplication. Or I can see if it's possible to protect the rows after data has been input - again this wouldn't solve the duplication issue if last row was still used).

Any ideas on a function that could help?

I'm researching Range.find and its friends at the moment (range.findnext etc)

[Toubkal]

Hello,

You can use HOUR function to prevent duplicate inputs within the same date/hour. Assuming you have a cell containing Date-time check the capture.

Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False
    Dim formDate
    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LstInptTime as String
    
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
        Set formDate = UserForm1.Label35
'Label on userform diplaying a set time on the half hour (if within a certain range)
        Set formTime = UserForm1.ComboBox1
'Find the lastRow to write after. 
    LastRow = ws.Range("E" & Rows.Count).End(xlUp).Row        '<--------- LastRow
    LstInptTime = ' <============ put here the cell containing date time 

If (Now > LstInptTime) And (Hour(Now) > Hour(LstInptTime)) Then    '<------- New Hour then new input in "LastRow+1"
    Range("E" & LastRow+1).Value = TextBox193.Text
    Range("G" & LastRow+1).Value = TextBox194.Text
    Range("I" & LastRow+1).Value = TextBox195.Text
    Range("K" & LastRow+1).Value = TextBox196.Text
    Range("M" & LastRow+1).Value = TextBox197.Text
    Range("O" & LastRow+1).Value = TextBox198.Text
    Range("Q" & LastRow+1).Value = TextBox199.Text
    Range("S" & LastRow+1).Value = TextBox200.Text
    Range("U" & LastRow+1).Value = TextBox201.Text

Elseif If (Now > LstInptTime) And (Hour(Now) = Hour(LstInptTime)) Then     '<---------- same Hour then overwrite last input. "LastRow"
    Range("E" & LastRow).Value = TextBox193.Text
    Range("G" & LastRow).Value = TextBox194.Text
    Range("I" & LastRow).Value = TextBox195.Text
    Range("K" & LastRow).Value = TextBox196.Text
    Range("M" & LastRow).Value = TextBox197.Text
    Range("O" & LastRow).Value = TextBox198.Text
    Range("Q" & LastRow).Value = TextBox199.Text
    Range("S" & LastRow).Value = TextBox200.Text
    Range("U" & LastRow).Value = TextBox201.Text
end if
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

[vhookup]

Pivot master.JPGHi thanks for the reply, I'm going to plug this in and see what happens,
LstInptTime cell - I don't have a cell with current time on the sheet they are all set times (on the half hour) so that charts can be created and display correctly. I only have column d which is full of times. I'll see if I can upload a copy of my destination worksheet.

Could the times already on the sheet be used?

as you can see - each day has 17 times on it and I need every day of the year total of 6000+ rows. and a lot of repeating dates and times.

my latest rework of the code with range.find (i tried offset in there too) with this code i get the code to run but the data doesn't appear in the sheet, any inclination as to why? it should be going into row 1957 if its reading the Ifs correctly (which by my guess it is not)

Private Sub Commandbutton1_Click()Application.ScreenUpdating = False
    Dim formDate
    Dim formTime As Variant
    Dim ws As Worksheet
    'Dim LastRow As Long
    
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
    Set formDate = UserForm1.Label35
'Combo box on userform diplaying a set time on the half hour (if within a certain range)
    Set formTime = UserForm1.ComboBox1


'Identifying date and time
    With ws
        Set c = .Range("$C$2:$C$6217").Find(formDate)
            If c Is formDate Then
                Set d = .Range("$D$2:$D$6217").Find(formTime)
            End If
            Set d = .Range("$D$2:$D$6217").Find(formTime)
                If d Is formTime Then
                d.Cells(1, 2).Value = TextBox193.Text
                d.Cells(1, 4).Value = TextBox194.Text
                d.Cells(1, 6).Value = TextBox195.Text
            End If
    End With


    'Range("G2").Value = TextBox194.Text
    'Range("I2").Value = TextBox195.Text
    'Range("K2").Value = TextBox196.Text
    'Range("M2").Value = TextBox197.Text
    'Range("O2").Value = TextBox198.Text
    'Range("Q2").Value = TextBox199.Text
    'Range("S2").Value = TextBox200.Text
    'Range("U2").Value = TextBox201.Text
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

I'll see what i can do with the code you supplied Toubkal

[Toubkal]

please can you post a capture of the userform.Label and the data in combobox (formtime)
I consider that :
1- You have a preformatted table with set Dates and times (like in the capture you posted).
2- The user cannot choose the Date, it is retrieved from userform.Label135 (so no worries about date)
3- The user can choose time from the Combobox1 then: we have to find that date/Time and write/overwrite the data.

Assuming these statements, I made test (see captures)

Private Sub Commandbutton1_Click()Application.ScreenUpdating = False
'    Dim formDate
'    Dim formTime As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim Ldate, ComboTime As String
    dim I as Integer
'Destination sheet
    Set ws = Sheet59
'Label on userform displaying current date
'    Set formDate = UserForm1.Label35
'Combo box on userform diplaying a set time on the half hour (if within a certain range)
'    Set formTime = UserForm1.ComboBox1

LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row      'LastRow for the loop

'Identifying date and time
Ldate = Trim(UserForm1.Label135.Caption)            'date retrieved from Label135  (w/o spaces)
ComboTime = Trim(UserForm1.ComboBox1.Value)            'time selected in the ComboBox1 (w/o spaces)        

For I = 1 To LastRow
    If (ws.Range("C" & I).Text = Ldate) And (ws.Range("D" & I).Text = ComboTime) Then  ' if and only if Date and Time match then Found we are at the right row number I 
        ws.Range("G" & I).Value = TextBox194.Text
        ws.Range("I" & I).Value = TextBox195.Text
        ws.Range("K" & I).Value = TextBox196.Text
        ws.Range("M" & I).Value = TextBox197.Text
        ws.Range("O" & I).Value = TextBox198.Text
        ws.Range("Q" & I).Value = TextBox199.Text
        ws.Range("S" & I).Value = TextBox200.Text
        ws.Range("U" & I).Value = TextBox201.Text
    End If
Next I
    
Dim x As Integer
For x = 193 To 201
    Me.Controls("textbox" & x).Value = ""
Next x
    Cancel = MsgBox("Data Input Successful")


Me.Hide


Application.ScreenUpdating = True
End Sub

[vhookup]

I am truly excited by this! I'm not back at the computer until Sunday, it's the first thing I'm doing when I get back on it! Thanks
Toubkal.

[vhookup]

Toubkal, before your help I was like


But now i am like THANK YOU!

Tried it this morning, worked like a charm!
SUCCESS.JPG

You requested to see the userform as well (I know this probably has little bearing on the solution now, but hopefully it will help users facing a similar issue understand and be able to apply it to their projects) this Userform has a Label showing the time, I changed this to the combobox to have the drop down list of times.

The reason I changed to combobox is that I was trying to code it so the label showed 06:30:00 IF time was >=06:15:00 and <=07:14:59 etc but I couldn't get it to work, I thought the less the user has to select on the form the better and having the data entry cells alone was simpler for the end user, so kind of copped out and put a combobox in.
Data form.JPG

You can use HOUR function to prevent duplicate inputs within the same date/hour. Assuming you have a cell containing Date-time check the capture.

Will this idea still work in this new code you have written?

[Toubkal]

Glad it helped.

I understand that you wan to reduce the human factor when entering data.
If you want to use a label instead of Combobox, and according to your condition: 06:30:00 IF time was >=06:15:00 and <=07:14:59, try the code below.
I used MouseMove event as a trigger to update Labelx.caption. Then you can use "Labelx.caption" in your CommandButton1_Click --> ComboTime = Trim(Labelx.caption) don't forget to change the "x" into your label number.

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim H, M
H = Hour(Time)
M = Minute(Time)
If M < 15 Then
    Userform1.Labelx.Caption = H - 1 & ":30:00"
Else
    Userform1.Labelx.Caption = H & ":30:00"
End If
End Sub

[vhookup]

Thank you that's worked great!

[Toubkal]

You're Welcome.
Please mark the thread as solved.

[vhookup]

Done.

[vhookup]

Hi Toubkal, I've just discovered an issue with the format in the code, I could work round it by changing the master/source sheet and adapt the code but this could have a knock on effect along the chain I think.

As i tested the code after 10am the issue didn't show itself until now, as an example the auto time code doesn't display 07:30:00 instead it displays 7:30:00 (it does this for anytime from 01:00:00- 09:59:59), could this be modified at all to display as such?

Me and my rudimentary understanding of coding have attempted

Private Sub Userform_Initialize()    Label35 = Format(Now(), "dd/mm/yyyy")
    Label34 = Format(Now(), "dddd")
    Label36 = Format("hh:mm:ss")

This did nothing.

Also:

Private Sub Userform_Initialize()    Label35 = Format(Now(), "dd/mm/yyyy")
    Label34 = Format(Now(), "dddd")
    Label36 = Format(Now(), "hh:mm:ss")

This did nothing.

Also:

 Dim H, M        H = Hour(Time)
        M = Minute(Time)
                    
            If M < 15 Then
                UserForm1.Label36.Caption = H - 1 & "hh:30:00"
            Else
                UserForm1.Label36.Caption = H & "hh:30:00"

and this

 Dim H, M        H = Hour(Time)
        M = Minute(Time)
                    
            If M < 15 Then
                UserForm1.Label36.Caption = HH - 1 & ":30:00"
            Else
                UserForm1.Label36.Caption = HH & ":30:00"

These also didn't work. and other variations

[vhookup]

This just returned FALSE. as did shortening Format to ("hh") I'm stumped. Google searches have returned little as this is a pretty specific and custom application of code, I'm hoping there is a simple solution. Thanks

 Dim H, M
        H = Hour(Time)
        M = Minute(Time)
                    
            If M < 15 Then
                UserForm1.Label36.Caption = H - 1 = Format("hh:mm:ss") & ":30:00"
            Else
                UserForm1.Label36.Caption = H = Format("hh:mm:ss") & ":30:00"
                       
            End If
            
            If H = 22 Then
                UserForm1.Label36.Caption = "22:15:00"
            End If