Sending Data to web form with checkboxes 1

[Pudsters]

Beginner at VBA, I created a button to send data from a worksheet to an online form - a simple timecard

Online Form: 3 checkboxes, 6 textboxes

chkboxJOB1
txtNameJOB1
txtHoursJOB1

chkboxJOB2
txtNameJOB2
txtHoursJOB2

chkboxJOB3
txtNameJOB3
txtHoursJOB3


What I want to do is if the web form is empty (assume it is the first job of the day), check the first chkbox and populate those two fields, and End. User goes to his next job, fills out a new worksheet, presses the button, now it checks the JOB2 box and populates that section. User goes to Job 3, fills out worksheet, sends to JOB3 section. FYI: The form saves/retains its values using local storage and gets submitted once at end of day.

HOW DO I DO THIS???

HERE IS MY BASIC CODE to just send info to first section:

Private Sub sendtimecard()

Dim ie As Object
Dim i As Integer
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.Navigate "

http://website.html"

Do
DoEvents
Loop Until ie.ReadyState = 4

ie.Document.getElementById("chkboxJOB1").Checked = True
ie.Document.getElementById("txtNameJOB1").Value = ThisWorkbook.Sheets("Sheet1").Range("A1")
ie.Document.getElementById("txtHoursJOB1").Value = ThisWorkbook.Sheets("Sheet1").Range("B1")

End Sub

 

[SkipVought]

Hi,

Something like this?

Private Sub sendtimecard()

    Dim ie As Object
    Dim i As Integer
    Dim JobNo As String
    
    JobNo = InputBox("Enter Job Number between 1 and 3")
    
    If JobNo >= "1" And JobNo <= "3" Then
        
        Set ie = CreateObject("InternetExplorer.Application")
        ie.Visible = True
        ie.Navigate "[URL unfurl="true"]http://website.html"[/URL]
        
        Do
        DoEvents
        Loop Until ie.ReadyState = 4
        
        With ie.Document
            .getElementById("chkboxJOB" & JobNo).Checked = True
            .getElementById("txtNameJOB" & JobNo).Value = _
                ThisWorkbook.Sheets("Sheet1").Range("A1")       '[b]does this reference change with JobNo?[/b]
            .getElementById("txtHoursJOB" & JobNo).Value = _
                ThisWorkbook.Sheets("Sheet1").Range("B1")       '[b]does this reference change with JobNo?[/b]
        End With
    End If
End Sub

Do the cell references change with JobNo?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Well, I want to do it without user intervention! No Input box! And no, the cells stay the same. I got it to work, but I know my code sucks and there must be a much easier way to do with some type of a loop.

If ie.Document.getElementById("checkboxIdj1").Checked = False Then

ie.Document.getElementById("checkboxIdj1").Checked = True
ie.Document.getElementById("name-j1").Value = Worksheets("Invoice").Range("A1")
ie.Document.getElementById("hours-j1").Value = Worksheets("Invoice").Range("B1")

Exit Sub

Else

If ie.Document.getElementById("checkboxIdj2").Checked = False Then

ie.Document.getElementById("checkboxIdj2").Checked = True
ie.Document.getElementById("name-j2").Value = Worksheets("Invoice").Range("A1")
ie.Document.getElementById("hours-j2").Value = Worksheets("Invoice").Range("B1")

Exit Sub

Else

If ie.Document.getElementById("checkboxIdj3").Checked = False Then

ie.Document.getElementById("checkboxIdj3").Checked = True
ie.Document.getElementById("name-j3").Value = Worksheets("Invoice").Range("A1")
ie.Document.getElementById("hours-j3").Value = Worksheets("Invoice").Range("B1")

Exit Sub

Else

MsgBox "Timecard is Full!"

End If

End If

End If

 

[SkipVought]

Private Sub sendtimecard()
    Dim i As Integer
    Dim ws As Worksheet
        
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .Navigate "[URL unfurl="true"]http://website.html"u[/URL]
        
        Do
            DoEvents
        Loop Until .ReadyState = 4
        
        With .Document
            For i = 1 To 3
                If Not .getElementById("chkboxJOB" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById("txtNameJOB" & i).Value = ws.Range("A1")
                    .getElementById("txtHoursJOB" & i).Value = ws.Range("B1")

                    GoTo Xit
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
    
Xit:
    Set ws = Nothing
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Pudsters,
Do you see how nicely Skip's code is presented / aligned ?
Could you do the same in your future posts, please?


---- Andy

There is a great need for a sarcasm font.

 

[Pudsters]

Skip, that code looks great, so much better, and I see what you did. But it doesn't work!

GoTo Exit and Exit: shows up in red an gives a Syntax error

Skip, I replaced GoTo Exit with Exit Sub and that got rid of the error and it compiles and works properly. Is that still proper use of code?

 

[SkipVought]

Sorry, I uses a reserve word, Exit. [BLUSH]

Just change Exit to something different like Xit, for instance.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Okay, thanks again, what a difference. Especially because I really have up to 8 jobs a day, not just 3 in the example. So that saved a lot of code!

 

[Andrzejek]

Hey Skip,

Would [blue]that[/blue] be the same? Just wondering....

...
                    .getElementById("txtHoursJOB" & i).Value = ws.Range("B1")

                    [s]GoTo Xit[/s] [blue]
                    Set ws = Nothing
                    Exit Sub[/blue]
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
    
[s]Xit:[/s]
    Set ws = Nothing
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Yes, I thought about doing that. Its a tossup. Actually I don’t like using GoTo. Its a good alternative.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Why would you need to set ws = Nothing twice? Or is the 2nd one only happens if the timecard is full?

 

[SkipVought]

Yes to the latter. If any check box is set, the Exit occurs before the last statements are executed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

And you don't really need to set it to Nothing, if the object is declared and set to something in the procedure. It will be "destroyed" when it falls out of scope anyway. (right?)
It is just a good coding practice to do so, clean after yourself


---- Andy

There is a great need for a sarcasm font.