Pop Up Window entry

[DanA1509]

Hi All,

I am currently producing a work number tracking spreadsheet for the people I work with and wanted to know if there was a way to generate a pop up window that created fields to enter the data for the job being inputted.

I currently have it just a simple macro (below) that enters a new line, adds a sequential number and adds both the date entered, as well as an expected finishing date. It does what I want it to do, however I am looking for it to be fool-proof as the last system was broken as people didn't fill it in fully.

Sub New_Line()
    Range("A4").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A4").Value = Range("A5").Value + 1
    Range("C4").Value = Now()
    Range("D4").Value = DateSerial(Year(Now()), Month(Now()) + 1, Day(Now() - 10))
End Sub

On the previous system it used a worksheet that did similar, with dropdown menus that auto-filled the sheet as well as inputted into the spreadsheet. That was produced by an old employee that isn't working on it any more.

Any and all help would be greatly appreciated,

All the best,
Dan

Edit: Apologies for the code not being very well formatted, the forum squashes it

 

[BrianMH]

It sounds like what you need is a form.

These tend to take quite a bit of time to code so you probably won't get anyone to build it for you but below is a link to a document guide to building one, it should get you started.

http://www.fontstuff.com/ebooks/free/fsuserforms.pdf

 

[DanA1509]

Thank you Brian, looks exactly what I need... once I've built the basic layout is there any chance you might be able to help me if there are any issues?

 

[BrianMH]

I'm sure if you have specific questions we will be happy to help. Just make sure you try to find the answer yourself first.

Thanks

 

[DanA1509]

Okay, I've developed the form from the link you've given me, it's opened via a button that uses the macro code:

Sub New_Line()
    Range("A4").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A4").Value = Range("A5").Value + 1
    Range("C4").Value = Now()
    Range("D4").Value = DateSerial(Year(Now()), Month(Now()) + 1, Day(Now() - 10))
    MPTR_Menu.Show
End Sub

Which opens the form window nicely, the form is coded as such:

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdClear_Click()
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub

Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
    If Me.comBooking.Value = "" Then
        MsgBox "Please enter name of Engineer booking job.", vbExclamation, "Job Booking"
        Me.comBooking.SetFocus
        Exit Sub
    End If
    
RowCount = Worksheets("Sheet1").Range("A4").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A4")
    .Offset(RowCount, 1).Value = Me.comBooking.Value
    .Offset(RowCount, 4).Value = Me.txtDrawingNum.Value
    .Offset(RowCount, 5).Value = Me.comCode.Value
    .Offset(RowCount, 6).Value = Me.txtWorkingNum.Value
    .Offset(RowCount, 7).Value = Me.comCustomer.Value
    .Offset(RowCount, 8).Value = Me.comProject.Value
    .Offset(RowCount, 9).Value = Me.txtDescription.Value
End With
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub

This allows for all the combo boxes to work correctly, and allows me to input the data I'm after. However when I click the 'Okay' button, it clears the information I've input but does not update the table.

I've read through the page several times and explored a few forms forum pages, but cannot tell where I'm going wrong. I hope it's not something really stupid, if it is I'm sorry for wasting your time.

 

[BrianMH]

As far as I can see you are inserting a new row at A4 which should be blank and ready for input. If that is the case you don't need to offset by rowcounts.

Try changing

RowCount = Worksheets("Sheet1").Range("A4").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A4")
    .Offset(RowCount, 1).Value = Me.comBooking.Value
    .Offset(RowCount, 4).Value = Me.txtDrawingNum.Value
    .Offset(RowCount, 5).Value = Me.comCode.Value
    .Offset(RowCount, 6).Value = Me.txtWorkingNum.Value
    .Offset(RowCount, 7).Value = Me.comCustomer.Value
    .Offset(RowCount, 8).Value = Me.comProject.Value
    .Offset(RowCount, 9).Value = Me.txtDescription.Value
End With

To

'note I've removed the rowcount completely
With Worksheets("Sheet1").Range("A4")
    .Offset(0, 1).Value = Me.comBooking.Value
    .Offset(0, 4).Value = Me.txtDrawingNum.Value
    .Offset(0, 5).Value = Me.comCode.Value
    .Offset(0, 6).Value = Me.txtWorkingNum.Value
    .Offset(0, 7).Value = Me.comCustomer.Value
    .Offset(0, 8).Value = Me.comProject.Value
    .Offset(0, 9).Value = Me.txtDescription.Value
End With

 

[DanA1509]

This is what appears when happening (I've also tried experimenting with different versions of the code on a blank file and it does the same thing, even with your updated code).

Honestly I'm not sure whats happening to cause this, and if it helps I can try loading the form so its easier to see.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[TD]Number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]30/10/13
[/TD]
[TD]20/10/13
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]30/10/13
[/TD]
[TD]20/11/13
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SMITH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SMITH
[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC
[/TD]
[TD]12345
[/TD]
[/TR]
</TBODY>[/TABLE]

EDIT: Actually your update works perfectly I forgot to remove the top line. Thank you extremly for your help Brian, your a saint.

 

[BrianMH]

Glad to help.