PDA

View Full Version : [SOLVED] input data from vba userform textbox to a spreadsheet cell depending on the time/date



vhookup
11-15-2018, 07:23 AM
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
11-15-2018, 09:13 AM
Set formDate = Userform1.Label35.Text
or maybe...

Set formDate = Userform1.Label35.Value
Change Userform1 name to suit. HTH. Dave

vhookup
11-15-2018, 09:25 AM
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
11-15-2018, 09:28 AM
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" :think:

Dave
11-15-2018, 11:22 AM
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
11-16-2018, 12:46 AM
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
11-16-2018, 01:06 AM
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
11-16-2018, 03:00 AM
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
11-16-2018, 03:29 AM
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
11-16-2018, 06:33 AM
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
11-16-2018, 06:58 AM
23206Hi 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
11-16-2018, 09:36 AM
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
11-16-2018, 02:38 PM
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
11-18-2018, 12:53 AM
Toubkal, before your help I was like :banghead::banghead::banghead::banghead::banghead::banghead::banghead::bang head::banghead:


But now i am like : pray2:: pray2::clap::clap::clap::bug::rofl::rofl: :thumb:thumb:thumb:thumb:thumb:thumb:thumb:rofl::rofl::bug::clap::clap::cla p:: pray2:: pray2::cloud9: THANK YOU!

Tried it this morning, worked like a charm!
23220

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.
23221



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
11-19-2018, 07:54 AM
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
11-19-2018, 02:23 PM
Thank you that's worked great!

Toubkal
11-19-2018, 03:41 PM
You're Welcome.:)
Please mark the thread as solved.

vhookup
11-19-2018, 04:00 PM
Done.:)

vhookup
11-21-2018, 02:47 AM
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
11-21-2018, 03:42 AM
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

Toubkal
11-21-2018, 01:36 PM
Hi,
I used Format function :



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) '<<<<< if Time = 07:13:00 / H will be "07" at this stage
M = Minute(Time)
If M < 15 Then
UserForm1.Label36.Caption = Format(H - 1, "##00") & ":30:00" '<<<< Here H - 1 will be 7 - 1 = 6, with Format function we will have 06
Else
UserForm1.Label36.Caption = Format(H, "##00") & ":30:00" '<<<< Same Here. BTW ":30:00" is just a compeltion text and not a formatting string.
End If
End Sub