I have created a bill template, on opening the template a form appears asking
for references, address details etc the user also selects from a comboBox the
amount of Costs or Disb they wish to enter.
they then click on create bill.
This then opens another form called costs where they enter the cost
narrative, amount and whether it is VAT chargeable or not. when you click on
finish this information goes into the 3rd row of a table in the document.
The for then reopens the amount of times needed to enter all costs, once all
costs are entered, a 3rd form appears to enter in Disbs.
The issue I am having is I do not want the users to be able to edit directly
onto the document so I would like all the narrative, (held in the 1st Column
from row 3 down) to appear in another userform, so that it can be edited
there.
or whereever the cusor is that row data to appear in the userform to be
edited.
Unfortunatley I can not find anything relating to how to do this??? I do not
know if it is possible to recall the userforms with the data or whether I
need to create a new user form holding only the information to be edited.
Any advise or help on this would be much appreciate.
Sorry if it is not clear what I am trying to do.
Thanks in advance.....
fumei - 18 Mar 2008 17:07 GMT
You have mutually exclusive ideas.
"I do not want the users to be able to edit directly onto the document "
And
"or whereever the cusor is that row data to appear in the userform to be
edited."
If they can not edit, then they can not put the cursor where ever they want.
You can lock the document for edits by protecting for forms. However, it
does just that. It locks the document for edits. They can not move the
cursor into the locked areas (Sections).
You can get the contents ("all the narrative, (held in the 1st Column
from row 3 down) to appear in another userform, so that it can be edited
there.) and put it in a userform. Although you need something to start up
that userform.
Say you have data in Cell(3, 1), Cell(4,1), Cell(5,1), Cell(6,1) - i.e row 3
down to row 6.
Option Explicit
Function CellText1(strIn As String)
CellText1 = Left(strIn, Len(strIn) - 2)
End Function
Private Sub UserForm_Initialize()
Dim aTable As Table
Set aTable = ActiveDocument.Tables(1)
' get cell contents and put in textboxes
TextBox1.Text = CellText1(aTable.Cell(3, 1).Range.Text)
TextBox2.Text = CellText1(aTable.Cell(4, 1).Range.Text)
TextBox3.Text = CellText1(aTable.Cell(5, 1).Range.Text)
TextBox4.Text = CellText1(aTable.Cell(6, 1).Range.Text)
Set aTable = Nothing
End Sub
Private Sub cmdUpdate_Click()
Dim aTable As Table
Set aTable = ActiveDocument.Tables(1)
' put textbox contents into cells
aTable.Cell(3, 1).Range.Text = TextBox1.Text
aTable.Cell(4, 1).Range.Text = TextBox2.Text
aTable.Cell(5, 1).Range.Text = TextBox3.Text
aTable.Cell(6, 1).Range.Text = TextBox4.Text
Set aTable = Nothing
Unload Me
End Sub
There are other ways. But how are to going to start this userform?
IF, the document is not protected for forms - i.e the user CAN move the
cursor where they want, and you want to get the data from the row the cursor
is on:
Private Sub UserForm_Initialize()
' get cell text and put in textbox
TextBox1.Text = CellText1(Selection.Range _
.Rows(1).Cells(1).Range.Text)
End Sub
Private Sub cmdOtherUpdate_Click()
' put textbox text into cell
Selection.Range _
.Rows(1).Cells(1).Range.Text = _
TextBox1.Text
Unload Me
End Sub
>I have created a bill template, on opening the template a form appears asking
>for references, address details etc the user also selects from a comboBox the
[quoted text clipped - 25 lines]
>
>Thanks in advance.....
Tony Strazzeri - 24 Mar 2008 05:55 GMT
> I have created a bill template, on opening the template a form appears asking
> for references, address details etc the user also selects from a comboBox the
[quoted text clipped - 12 lines]
> from row 3 down) to appear in another userform, so that it can be edited
> there.
Allow me to restate my understanding of what you are doing before I
explain my general approach to this kind of requirement.
You are getting cost data for multiple items (of costs). You want to
show each of these so that if necessary they can be individually
changed before you move on to the disbursement side of essentially
doing the same thing.
My usual approach to this sort of thing is to allow each field/part of
each disbursement record to be input into separate fields on a
userform. I then have a button that adds the parts to a listbox on
the form where each records can be seen and cleas the individual
textbox fields ready for the next record. I then provide buttons to
allow a record from the listbox or listview to be placed back into the
individual textboxes for editing and replacing back to the listbox/
listview.
When finished I use the data from the listbox/listview to populate the
document. The same approach can then be used for the disbursements.
In the past I have also used the multipage control with a tab for the
costs and a separate one for the disbursements.
Additionally if the requirement is there I have added buttons to re-
organise the order of the items in the listbox/listview if necessary.
Also (either using another button or the KeyUp event as in the
example code below for a listview) I can allow for an item already
inout to be deleted.
Private Sub ListView1_KeyUp(KeyCode As Integer, ByVal Shift As
Integer)
Dim ThisItem
Dim thisLI As ListItem
ThisItem = ListView1.SelectedItem.Index
If KeyCode = vbKeyDelete And ListView1.SelectedItem <> -1 Then
ListView1.ListItems.Remove (ThisItem)
'now renumber any items below this
For ThisItem = ThisItem To ListView1.ListItems.Count
Set thisLI = ListView1.ListItems(ThisItem)
thisLI.Text = Val(thisLI.Text) - 1
thisLI.Key = strLI_Key_Prefix & thisLI.Text
Next
End If
End Sub
Hope this helps. Post back if you need further info.
BTW the main difference (userwise) between a listbox and a listview is
that it is easier for the user to adjust the columns width to see the
contents of a long item in a column of data.
Cheers
TonyS.