USD $30.00 For excel user form to enter, or edit, data.

steve400243 · Feb 22nd 2017

Hello,

I need to get this spreadsheet to have a User form, like the excel data entry window, to pop up automatically when the sheet is selected. This would be used to enter new information based on the date selected, or edit the current data. This would be a new form every week. Monday thru Sunday.

• When new information is entered in the User form, it would put that data in the correct day. There can be as many as 10-15 per day. Some days would have less, so it would need to auto adjust to how many rows would be needed per day.
• If the main sheet could be locked so no one could add or change data without going to the user form.
• I would like 1 empty row between completed days

I hope this makes sense? Thank you for your time please let me know if more information is needed.

**KjBox** · Feb 23rd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

I can do this for you.

steve400243 · Feb 23rd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Quote from KjBox;786240

I can do this for you.

Thank you!

**KjBox** · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Payment received, many thanks. File is attached, read the stuff on the "Notes" sheet.

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Excellent work KjBox, Thank you - When i try to add a new load i'm getting an "Run-time error '9' Subscript out of range" error. May be doing something wrong,

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Also the load number will need to a user entered number. This is provided to us by the Carrier and is not known until we schedule the load. This looks awesome by the way! Very nice work

**KjBox** · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

OK I can change that for the Load number. The only thing is, load numbers need to be unique for every load in order for the code to locate the correct Load when one is selected for editing. Would it be OK to add code that would check for a duplicate Load number then change the new Load Number a bit?

For example, if Load Number T1234 was found to already exist in the Raw Data Table then the new Load would have it's number changed to T1234-(2). Hopefully duplications would never occur, but need to be handled just in case they do.

Can you re-create the error you got, then click "Debug" on the message box, that will open the VB Editor and the line of code that caused the error will be highlighted in yellow. Let me know what line of code in what Sub gets highlighted.

Thanks.

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Here is the error -

Quote

.ListRows(i).Range.Columns(CInt(ctl.Tag)) = ctl

And for the load number, anything that you can do is fine as long as it can be edited.

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Here is the Sub -

Quote

Private Sub cmdSubmit_Click()
Dim i As Long, ctl As MSForms.Control

**KjBox** · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Changed that whole Sub to this

Code

Private Sub cmdSubmit_Click()
    Dim i As Long, ctl As MSForms.Control
    
    Application.ScreenUpdating = 0
    If Application.Caller = "btnAdd" Then
        With RawData.ListObjects("tblRawData")
            If Application.CountA(.ListRows(1).Range) > 0 Then .ListRows.Add
            For Each ctl In Me.Controls
                If ctl.Tag <> "" Then
                    If TypeName(ctl) = "TextBox" _
                    Or TypeName(ctl) = "ComboBox" _
                    Or TypeName(ctl) = "Label" _
                    Or TypeName(ctl) = "DTPicker" Then
                        If IsNumeric(ctl) Then
                            .ListRows(.ListRows.Count).Range.Columns(CInt(ctl.Tag)) = CLng(ctl)
                        Else
                            .ListRows(.ListRows.Count).Range.Columns(CInt(ctl.Tag)) = ctl
                        End If
                    End If
                End If
            Next
            If optPallets Then
                .ListRows(.ListRows.Count).Range.Columns(3) = "Pallets"
            Else
                .ListRows(.ListRows.Count).Range.Columns(3) = "Floorloaded"
            End If
        End With
        Schedule.Unprotect [PWD]
        [WkPlusMinus] = (Int((CDbl(dpDelDt) - CDbl(Date)) / 7) * 7) + 7
    ElseIf Application.Caller = "btnEdit" Then
        i = Application.Match(CLng(lblLoadNo), [tblRawData[Load Number]], 0)
        With RawData.ListObjects("tblRawData")
            For Each ctl In Me.Controls
                If ctl.Tag <> "" Then
                    If TypeName(ctl) = "TextBox" _
                    Or TypeName(ctl) = "ComboBox" _
                    Or TypeName(ctl) = "Label" _
                    Or TypeName(ctl) = "DTPicker" Then
                        If IsNumeric(ctl) Then
                            .ListRows(i).Range.Columns(CInt(ctl.Tag)) = CLng(ctl)
                        Else
                            .ListRows(i).Range.Columns(CInt(ctl.Tag)) = ctl
                        End If
                    End If
                End If
            Next
            If optPallets Then
                .ListRows(i).Range.Columns(3) = "Pallets"
            Else
                .ListRows(i).Range.Columns(3) = "Floorloaded"
            End If
        End With
    End If
    ScheduleReset
    ScheduleUpdate
    Unload Me
    Application.Goto Schedule.[a1]
    Schedule.Protect [PWD], , , , 1
    
End Sub

Display More

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Excellent, I have updated the Sub and it adds the loads, however once the "add load" button is clicked and it adds the load it moves the sheet to the next week range?

It also locks the sheet, after each update, requiring the password to be entered to change it back to the current week.

**KjBox** · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Yes, it will advance the Schedule Tables to show the weekly schedule for the week for which the newly added Load is scheduled, this was so that you can quickly and easily check how the schedule for that week has changed.

I can stop it doing that if you prefer, and maybe have a message to confirm that "The schedule for Week commencing <date in here> has been updated"?

I just noticed that the spin button needs the sheet to be unprotected, even though protection is set to UserInterfaceOnly = True (that should allow code to run on a protected sheet, but does not always work!)

I have modified the protection properties for certain cells and now the spin button works without needing to unprotect the sheet.

It is 1:40AM for me now, so I will do the Load Number change in the morning, falling asleep here now!

But here is the file with cell protection changed.

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Thank you Sir, really appreciate this, I will continue to test and make notes on what is needed, if anything.

**KjBox** · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

OK that would be good.

If you update the Raw Data sheet with a few actual Customers and Carriers in the Lists plus some actual Load details in the Raw Data Table and attach it here, then I can make a nice clean version for you to start working with.

steve400243 · Mar 2nd 2017

Re: USD $30.00 For excel user form to enter, or edit, data.

Quote from KjBox;786916

OK that would be good.

If you update the Raw Data sheet with a few actual Customers and Carriers in the Lists plus some actual Load details in the Raw Data Table and attach it here, then I can make a nice clean version for you to start working with.

Good Morning, Hope you got some rest! Please have a look at the attached, and let me know your thoughts. Thank you.

USD $30.00 For excel user form to enter, or edit, data.

Participate now!

Share