Hiya all!

I recently posted a general inquiry here a few days ago. Tom Ogilvy
responded with a macro that worked well based on my description.

It can be found he
http://groups.google.ca/groups?hl=en...%3D10%26sa%3DN

For some reason, I cannot respond to this thread so I decided to
create a new one with a much more detailed description as to what I
need done.

I will first go ahead and describe the userform for you all. There
are 9 text boxes, one check box with a text box required if checked
and 3 combo boxes with already defined menus for the pull downs.

The spreadsheet that I need the inputed data in the userform
transfered over is setup in 22 columns.

I went ahead and named everything for this process to go a little
smoother. I'll also list which boxes correspond with the proper column
in the spreadsheet.

txtdate (Column A)
txtstart (B)
txtduration (C)
txtresponse (D)
txtarrived (E)
txtcause (F)
txtcustomers (G)
txtlocation (H)
txtequip (I)
chkfollow (Associated text box is txtfollow) (P)
comboOEB (V)
comboFeeder (JKLMN)
combokelcom (U)

Once there is info in all boxes except for the possible check box, the
macro would input this data in an inserted row based on column A and
B. If not, there should be a prompt/error stating that a specific
piece of information is missing. Please remember that I have
subtotals for each month as per my last post. Same dates and times
can occur, which is something I did not specify last time around.

With regards to "comboFeeder", there are 5 possible selections. Each
one corresponds to a column (Either J, K, L, M or N) and the chosen
Feeder should mark an X in the appropriate cell.

Another possible stumper could be involved with the "chkfollow" box.
If checked, txtfollow becomes enabled. If enabled, the text inputted
in the userform should be inserted as a comment in column P. When
chkfollow is checked it should also insert the words More Work into
the cell in column P and highlight the cell in blue.

I believe that's it. This may seem like quite a bit however I'm sure
it's rather simple for those who are familiar with code for userforms.
I am very unfamiliar with userforms and hope to learn a lot from this
experience.

If you have any questions simply post here or email me.

Thanks in advance,

Kris Taylor
www.QuestOfAges.org Administrator

Kris
You might try something like this:

'Assuming you have the Date in Col A of your worksheet
Then insert a new col if needed as Col B
In B1 enter =Month(A1) and copy this down as far as needed.
Format col B as General so it will show as 1 for Jan, 2 for Feb etc
This col can be hidden as you like

Then use the following code to
1- remove existing Subtotal
2- Add a new record to you database at the END
3- Sort your db on the Date col
4- Add new Subtotals
----------------------------------------
Option Explicit

Sub PostUserFormToSheet()

Dim Col As Integer
Dim TotCols As Integer

Col = 2 'Set col = column # that has the Month in it
TotCols = 4 'set to the total number of columns in your db

'Your code to select a cell within your db

'Clear old SubTotals
Selection.RemoveSubtotal

'Add new record in db
'find LAST ROW
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell = Me.Textbox1
ActiveCell.Offset(0, 1) = Me.Textbox2
'continue populating the db cells

'Sort the db
ActiveCell.Sort Key1:=Range(ActiveCell.Address), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Insert New Subtotals
Selection.Subtotal GroupBy:=Col, Function:=xlSum,
TotalList:=Array(TotCols), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

On your Userform you will have an OK button. In the OK_Click routine
you will want to do some error checking to see (among other things
that all the boxes are filled, etc)
Then call the above sub (which is in a standard module) with
the code line:
PostUserFormToSheet
Alternatively, just add the code lines (sans Macro name and "End Sub")
directly into
your OK_Click routine

"Kris Taylor" wrote:

Hiya all!

I recently posted a general inquiry here a few days ago. Tom Ogilvy
responded with a macro that worked well based on my description.

It can be found he
http://groups.google.ca/groups?hl=en...%3D10%26sa%3DN

For some reason, I cannot respond to this thread so I decided to
create a new one with a much more detailed description as to what I
need done.

I will first go ahead and describe the userform for you all. There
are 9 text boxes, one check box with a text box required if checked
and 3 combo boxes with already defined menus for the pull downs.

The spreadsheet that I need the inputed data in the userform
transfered over is setup in 22 columns.

I went ahead and named everything for this process to go a little
smoother. I'll also list which boxes correspond with the proper column
in the spreadsheet.

txtdate (Column A)
txtstart (B)
txtduration (C)
txtresponse (D)
txtarrived (E)
txtcause (F)
txtcustomers (G)
txtlocation (H)
txtequip (I)
chkfollow (Associated text box is txtfollow) (P)
comboOEB (V)
comboFeeder (JKLMN)
combokelcom (U)

Once there is info in all boxes except for the possible check box, the
macro would input this data in an inserted row based on column A and
B. If not, there should be a prompt/error stating that a specific
piece of information is missing. Please remember that I have
subtotals for each month as per my last post. Same dates and times
can occur, which is something I did not specify last time around.

With regards to "comboFeeder", there are 5 possible selections. Each
one corresponds to a column (Either J, K, L, M or N) and the chosen
Feeder should mark an X in the appropriate cell.

Another possible stumper could be involved with the "chkfollow" box.
If checked, txtfollow becomes enabled. If enabled, the text inputted
in the userform should be inserted as a comment in column P. When
chkfollow is checked it should also insert the words More Work into
the cell in column P and highlight the cell in blue.

I believe that's it. This may seem like quite a bit however I'm sure
it's rather simple for those who are familiar with code for userforms.
I am very unfamiliar with userforms and hope to learn a lot from this
experience.

If you have any questions simply post here or email me.

Thanks in advance,

Kris Taylor
www.QuestOfAges.org Administrator

Kris,
The following code is a modification of Tom's code to show you where to
place your userform information. You will need to set edit check for require
information. This modification does not show the Checkbox or combo's. You
need to setup the combo with information that you want to be displayed then
selected. HTH. If you want you can contact me.

Charles

Private Sub CommandButton1_Click()
Dim rng As Range
Dim dt As Date
Dim rng1 As Range
Set rng1 = Nothing
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
dt = CDate(TextBox1.Text)
For Each cell In rng
If IsDate(cell) Then
If cell.Value = dt Then
MsgBox "Date already exist"
ElseIf cell.Offset(1, 0).Value dt Then
cell.Offset(1, 0).EntireRow.Insert
Set rng1 = cell.Offset(1, 0)
Exit For
End If
End If
Next
If Not rng1 Is Nothing Then
With UserForm1
rng1.Value = .txtdate.Text
rng1(, 2).Value = .txtstart.Text
rng1(, 3).Value = .txtduration.Text
rng1(, 4).Value = .txtresponse.Text
rng1(, 5).Value = .txtarrived.Text
rng1(, 6).Value = .txtcause.Text
rng1(, 7).Value = .txtcustomer.Text
rng1(, 7).Value = .txtlocation.Text
rng1(, 8).Value = .txtequip.Text

End With
End If


End Sub
"gocush" wrote in message
...
Kris
You might try something like this:

'Assuming you have the Date in Col A of your worksheet
Then insert a new col if needed as Col B
In B1 enter =Month(A1) and copy this down as far as needed.
Format col B as General so it will show as 1 for Jan, 2 for Feb etc
This col can be hidden as you like

Then use the following code to
1- remove existing Subtotal
2- Add a new record to you database at the END
3- Sort your db on the Date col
4- Add new Subtotals
----------------------------------------
Option Explicit

Sub PostUserFormToSheet()

Dim Col As Integer
Dim TotCols As Integer

Col = 2 'Set col = column # that has the Month in it
TotCols = 4 'set to the total number of columns in your db

'Your code to select a cell within your db

'Clear old SubTotals
Selection.RemoveSubtotal

'Add new record in db
'find LAST ROW
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell = Me.Textbox1
ActiveCell.Offset(0, 1) = Me.Textbox2
'continue populating the db cells

'Sort the db
ActiveCell.Sort Key1:=Range(ActiveCell.Address), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Insert New Subtotals
Selection.Subtotal GroupBy:=Col, Function:=xlSum,
TotalList:=Array(TotCols), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

On your Userform you will have an OK button. In the OK_Click routine
you will want to do some error checking to see (among other things
that all the boxes are filled, etc)
Then call the above sub (which is in a standard module) with
the code line:
PostUserFormToSheet
Alternatively, just add the code lines (sans Macro name and "End Sub")
directly into
your OK_Click routine

"Kris Taylor" wrote:

Hiya all!

I recently posted a general inquiry here a few days ago. Tom Ogilvy
responded with a macro that worked well based on my description.

It can be found he
http://groups.google.ca/groups?hl=en...%3D10%26sa%3DN

For some reason, I cannot respond to this thread so I decided to
create a new one with a much more detailed description as to what I
need done.

I will first go ahead and describe the userform for you all. There
are 9 text boxes, one check box with a text box required if checked
and 3 combo boxes with already defined menus for the pull downs.

The spreadsheet that I need the inputed data in the userform
transfered over is setup in 22 columns.

I went ahead and named everything for this process to go a little
smoother. I'll also list which boxes correspond with the proper column
in the spreadsheet.

txtdate (Column A)
txtstart (B)
txtduration (C)
txtresponse (D)
txtarrived (E)
txtcause (F)
txtcustomers (G)
txtlocation (H)
txtequip (I)
chkfollow (Associated text box is txtfollow) (P)
comboOEB (V)
comboFeeder (JKLMN)
combokelcom (U)

Once there is info in all boxes except for the possible check box, the
macro would input this data in an inserted row based on column A and
B. If not, there should be a prompt/error stating that a specific
piece of information is missing. Please remember that I have
subtotals for each month as per my last post. Same dates and times
can occur, which is something I did not specify last time around.

With regards to "comboFeeder", there are 5 possible selections. Each
one corresponds to a column (Either J, K, L, M or N) and the chosen
Feeder should mark an X in the appropriate cell.

Another possible stumper could be involved with the "chkfollow" box.
If checked, txtfollow becomes enabled. If enabled, the text inputted
in the userform should be inserted as a comment in column P. When
chkfollow is checked it should also insert the words More Work into
the cell in column P and highlight the cell in blue.

I believe that's it. This may seem like quite a bit however I'm sure
it's rather simple for those who are familiar with code for userforms.
I am very unfamiliar with userforms and hope to learn a lot from this
experience.

If you have any questions simply post here or email me.

Thanks in advance,

Kris Taylor
www.QuestOfAges.org Administrator

@gocush: Thanks for the effort, however my subtotals aren't typical
ones and cannot be recreated using the subtotals command. I therefore
did not play around with your code too much.

@Charles: This code works well like Tom's, however I have no idea how
to get teh check box and the combo box that is spread out over several
cells depending on entry to work. Thoughts on this?

Also, on a more important note, this macro does not insert entries in
which a month does not already exist in the database. Please remember
about the subtotals for each month when considering this problem.

My current code is as follows:
Private Sub btnOk_Click()
Dim rng As Range
Dim dt As Date
Dim rng1 As Range
Set rng1 = Nothing
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
dt = CDate(txtdate.Text)
For Each cell In rng
If IsDate(cell) Then
If cell.Value = dt Then
MsgBox "Date already exist"
ElseIf cell.Offset(1, 0).Value dt Then
cell.Offset(1, 0).EntireRow.Insert
Set rng1 = cell.Offset(1, 0)
Exit For
End If
End If
Next
If Not rng1 Is Nothing Then
With frmLaSalleOutage
rng1.Value = .txtdate.Text
rng1(, 2).Value = .txtstart.Text
rng1(, 3).Value = .txtduration.Text
rng1(, 4).Value = .txtresponse.Text
rng1(, 5).Value = .txtarrived.Text
rng1(, 6).Value = .txtcause.Text
rng1(, 7).Value = .txtcustomers.Text
rng1(, 8).Value = .txtlocation.Text
rng1(, 9).Value = .txtequipment.Text
rng1(, 21).Value = .ComboKelcom.Text
rng1(, 22).Value = .ComboOEB.Text

End With
End If
Unload Me
End Sub

Thanks,

Kris Taylor
www.QuestOfAges.org Administrator

Kris,

wrote in message
ups.com...
@gocush: Thanks for the effort, however my subtotals aren't typical
ones and cannot be recreated using the subtotals command. I therefore
did not play around with your code too much.

@Charles: This code works well like Tom's, however I have no idea how
to get teh check box and the combo box that is spread out over several
cells depending on entry to work. Thoughts on this?

Also, on a more important note, this macro does not insert entries in
which a month does not already exist in the database. Please remember
about the subtotals for each month when considering this problem.

My current code is as follows:
Private Sub btnOk_Click()
Dim rng As Range
Dim dt As Date
Dim rng1 As Range
'''''''''''''''''''' Added '''''''''''''
Dim i as Integer
Dim j as intenger
Dim k as integer
'''''''''''''''''''''''''''''''''''''''''''''''''' '''
Set rng1 = Nothing
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
dt = CDate(txtdate.Text)
For Each cell In rng
If IsDate(cell) Then
If cell.Value = dt Then
MsgBox "Date already exist"
ElseIf cell.Offset(1, 0).Value dt Then
cell.Offset(1, 0).EntireRow.Insert
Set rng1 = cell.Offset(1, 0)
Exit For
End If
End If
Next
If Not rng1 Is Nothing Then
With frmLaSalleOutage
rng1(,1).Value = .txtdate.Text<<<<<<<<< Corrected to show range
location
rng1(, 2).Value = .txtstart.Text
rng1(, 3).Value = .txtduration.Text
rng1(, 4).Value = .txtresponse.Text
rng1(, 5).Value = .txtarrived.Text
rng1(, 6).Value = .txtcause.Text
rng1(, 7).Value = .txtcustomers.Text
rng1(, 8).Value = .txtlocation.Text
rng1(, 9).Value = .txtequipment.Text
''''''''''''''''''''''''''' Added
'''''''''''''''''''''''''''''''''''''''''''''
If .chkfollow.Value = True Then
rng1(, 16).Value = "What?"<<<<< you need to supply info not sure
what you wanted
End If
For i = 0 To .ComboKelcom.ListCount - 1
If .ComboKelcom.Selected(i) Then
rng1(, 21).Value = .ComboKelcom.List(i)
End If
Next
For k = 0 To .ComboOEB.ListCount - 1
If .ComboOEB.Selected(k) Then
rng1(, 22).Value = .ComboOEB.List(k)
End If
Next
For j = 0 To .comboFeeder.ListCount - 1
If .comboFeeder.Selected(j) Then
rng1(, 10).Value = .comboFeeder.List(j)
rng1(, 11).Value = .comboFeeder.List(j)
rng1(, 12).Value = .comboFeeder.List(j)
rng1(, 13).Value = .comboFeeder.List(j)
rng1(, 14).Value = .comboFeeder.List(j)
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''
End With
End If
Unload Me
End Sub

Thanks,

Kris Taylor
www.QuestOfAges.org Administrator

@Charles: I haven't had a whole lot of time to really play with the
new code as of yet. Just posting to keep you on top of things.
Thanks for your help thus far!

Kris Taylor
www.QuestOfAges.org