Solved: Improving the AddRow code

[Richardall]

I would like to know if the add row function could be improved a little more.



I would like to add a control icon to a form that would allow the user to manipulate a table by growing it or shrinking it.



I am currently using this code to add a row if the answer is yes. This code duplicate the row and fills in the fields to match the existing row.



Sub SpecSheet_AddRow()

response = MsgBox("Add Additional Row?", vbQuestion + vbYesNo)

If response = vbYes Then

Dim rownum As Integer, i As Integer

ActiveDocument.Unprotect

Selection.Tables(1).Rows.Add

rownum = Selection.Tables(1).Rows.Count

For i = 1 To Selection.Tables(1).Columns.Count

ActiveDocument.FormFields.Add Range:=Selection.Tables(1).Cell(rownum, i).Range, Type:=wdFieldFormTextInput

Next i

Selection.Tables(1).Cell(Selection.Tables(1).Rows.Count, Selection.Tables(1).Columns.Count).Range.FormFields(1).ExitMacro = "SpecSheet_addrow"

Selection.Tables(1).Cell(Selection.Tables(1).Rows.Count, 1).Range.FormFields(1).Select

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End If

End Sub



This code has done well until somebody answered yes but meant to say no. Because this is a locked form, they couldn?t delete the added row and ended up sending a quote to a customer with a dead line?



I would like to see if the functions in InfoPath can be recreated in VBA. For licensing reasons, InfoPath isn?t available to me in this application.



Here is what I would like to try;

When the user is working in any cell on the row of a table. A control icon appears next to that row. If the cursor is clicked on the icon, a pull-down menu with a few options appears. These options would be to insert a new row above the current row, insert a new row below the current row, remove the current row and perhaps clear the current row.



I would like to make the form better, all suggested will be appreciated. I'm new to the VBA coding and I'm trying to learn. Please be patient and please anotate all the code so I can learn and understand.

I have attache an image to help see the idea.

TYIA
Richard

[fumei]

You want this control icon to be dynamic? It appears ONLY beside the row the cursor is in?

Have you created any control icons like your dropdown arrow in you image?

[Richardall]

Yes, I would like the icon to appear when in the current row. If that mans it dynamic, then I guess that's what I'm looking for. Is it doable?



As for the icon, it could just be a simple control button. The arrow image might be nice, but not critical


Thanks for asking. I knew I wouldn't explain well in the first try.

Richard

[fumei]

Very complicated, but it may be doable.

If the control icon is to "appear" ONLY for the row the selection is in, then:

A - you actually create the control when the selection is in the row, then destroy it when the selection moves out of that row. I would strongly suggest that this is NOT a good option.

B - you have controls for each row. The Selection makes each one visible for the row it is in. This too is a huge amount of code to work out.

What am I saying? Yes, it is POSSIBLE, but just because something is possible, does not mean it is reasonable to do so. There is far too much crap in the world that is done simply because some CAN.

OK. The issue is for a decent analysis of what is needed. The simple answer to the issue of - I need to remove this row, I need to add another row - is actually very simple.

There is ALREADY a way to do this. Depending on your version, just use Table > Delete (or Insert). There you go. Why suck up resources and development time to create code that does something you can already do rather easily?

I am walking this through for you.....EXCEPT, in your case the document is protected for forms. So Table > Insert (or delete) is not available.

OK, then unprotect it, just like you have for making a new row.

Run a different userform (maybe by a keyboard shortcut), or use an inputbox, to get input from the user. Then run some code like:

Selection.InsertRowsAbove 1 (inserts a row above)
Selection.Rows.Delete (deletes the current row)

What I am saying is that, yes, individual controls for rows is POSSIBLE, but unless you have a serious need for such large amount of code, it seems out of line to do so. Remember that if you would have such controls you have to build them, test them, and perform good error trapping on every one of them.

[MOS MASTER]

Hi,

This sort of stuff is not easy..Like Gerry mentioned...but not impossible!

However this requires testing and I have little time to rebuild a scenario.

Do you have a copy of your project to attach so we can test on that?

[Richardall]

Here is a copy of the form currently in use I stripped it down a bit as the original is 11 pages long. I left all the parts associated to the macros I'm running. You'll see that the code I currently have will add the row matching the number of columns and fields.



I understand it's difficult, but any suggestions for better code would be appreciated. For the record, I didn't write all this code exclusively. I found bits and pieces in this forum and another. Like I said, I'm just beginning to learn.

Perhaps the idea might be to see what is doable and it may be usable by others for their forms.

Thank you again.
Richard

[MOS MASTER]

Hi Richard,

Ok thanks I'll look at it as soon as I'm done in the KB forum!

[MOS MASTER]

Hi Richard,

Ok this is how I would write the Addrow sub:[vba]
Option Explicit 'Always use this at the top of your code module!

Sub SpecSheet_AddRow()
Dim response As Integer
Dim rownum As Integer
Dim iCnt As Integer
Dim iRows As Integer
Dim iCols As Integer
Dim oTable As Word.Table

response = MsgBox("Add Additional Row?", vbQuestion + vbYesNo)

If response = vbYes Then
Application.ScreenUpdating = False

With ActiveDocument
If .ProtectionType <> wdNoProtection Then .Unprotect

Set oTable = Selection.Tables(1)
With oTable
.Rows.Add
iRows = .Rows.Count
iCols = .Columns.Count
End With

For iCnt = 1 To iCols
.FormFields.Add Range:=oTable.Cell(iRows, iCnt).Range, _
Type:=wdFieldFormTextInput
Next

With oTable
.Cell(iRows, iCols).Range.FormFields(1).ExitMacro _
= "SpecSheet_addrow"
.Cell(iRows, 1).Select
End With

.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With
End If

Application.ScreenUpdating = True
Set oTable = Nothing
End Sub
[/VBA]

It's not shorter but more sollid.

HTH,

[fumei]

Except Joost, the original questions is regarding a specific scenarion. Ok oooopps..darn it DID add a row, but I don't WANT to.

Now what?

You got to delete it. My point being is that while possibly imporived by your code, the ADDing part was pretty much done. They wasn a VASTLY improved functionality - to be able to delete/add a row at will. Which, as stated exists already.

[MOS MASTER]

Hi Gerry,

Ow?? I must have mist that. These sort of things happen to me when the questions are so long I miss stuff and look to much at the title.

I'll revisit this question tomorrow and see if I understand it then.

[Richardall]

Have you had a chance to revisit the orginal request? I would really like to see this in action.

Thank you in advance.

[MOS MASTER]

Have you had a chance to revisit the orginal request? I would really like to see this in action.

Thank you in advance.

Well I was still hoping on a reply from you. If the poster stay's silent I always presume the question is solved.

I'll post a sample later this night have to do other stuff first.

[MOS MASTER]

Hi Richard,

Ok this code will add two buttons on the context (right-click) menu when you're in a formfield and removes them when the doc is closed:[vba]

Option Explicit
Private Sub Document_Open()
Dim oButton As Office.CommandBarControl
With Application
.CustomizationContext = NormalTemplate

With .CommandBars("Form Fields")
Set oButton = .Controls.Add
With oButton
.Caption = "Add Row"
.FaceId = 3682
.Style = msoButtonIconAndCaption
.OnAction = "basTable.CustomAddRow"
.BeginGroup = True
End With
Set oButton = .Controls.Add
With oButton
.Caption = "Delete Row"
.FaceId = 3194
.Style = msoButtonIconAndCaption
.OnAction = "basTable.CustomDeleteRow"
End With
End With
End With
Set oButton = Nothing
End Sub
Private Sub Document_Close()
On Error Resume Next
Application.CustomizationContext = NormalTemplate
With Application.CommandBars("Form Fields")
.Controls("Add Row").Delete
.Controls("Delete Row").Delete
End With
End Sub
[/VBA]

And here is the code for the "Add Row" & "Delete Row" button.[vba]
Option Explicit
Sub CustomAddRow()
Dim iCnt As Integer
Dim iRows As Integer
Dim iCells As Integer
Dim oTable As Word.Table
If Selection.Information(wdWithInTable) Then
Application.ScreenUpdating = False

With ActiveDocument
If .ProtectionType <> wdNoProtection Then .Unprotect

Set oTable = Selection.Tables(1)
With oTable
.Rows.Add
iRows = .Rows.Count
iCells = .Rows(iRows).Cells.Count
End With

For iCnt = 1 To iCells
.FormFields.Add Range:=oTable.Cell(iRows, iCnt).Range, _
Type:=wdFieldFormTextInput
Next

oTable.Cell(iRows, 1).Select

.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With

Application.ScreenUpdating = True
Set oTable = Nothing
End If
End Sub
Sub CustomDeleteRow()
If Selection.Information(wdWithInTable) Then
With ActiveDocument
If .ProtectionType <> wdNoProtection Then .Unprotect

Selection.Rows(1).Delete

.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End With
End If

End Sub
[/Vba]

So just stand on a formfield right click the mouse and choose add or delete row.

Didn't have much time to test so have fun testing.

Enjoy!

[Richardall]

Very cleaver solution... I'll try it give you any feedback.

Thank you,

[MOS MASTER]

Ok your welcome!