I am almost getting to the end of my project! I have a series of userforms
collecting and writing data to an excel spreadsheet, my struggle now is how I
could give users the option to amend a particular record. I can search for
records by a unique reference each one has, but how can I then pull this
information back into the appropriate userform so the user can made an
amendment to that particular record?

Any pointers in the right direction gratefully received!

--
Zani
(if I have posted here, I really am stuck!)

If it's set up as I think you describe (a spreadsheet containing all
the info for a record number along that same row), then simply reverse
the process.

When you write the data to the user form, you've probably got a button
that runs a macro to transfer the data to the cells from your textboxes
such as

Sheets("Data").Cells(14,37).Value = UserForm1.TextBox1.Text (or
similar).

To get the data back to the userform, just reverse the statement and
place it in a suitable event module:

UserForm1.TextBox1.Text = Sheets("Data").Cells(14,37).Value

Thank you so much for that - I can't believe it was as easy as that - works
fantastically for all the textboxes and optionsbuttons. Here comes the
tricky bit though - on this userform there are 3 listboxes the first being
the main one, and then two and three have values dependent on the value of
the first and then the second.

I can get my list box entries back into my userform, but then how can I
highlight what the selected option was when the original data was stored?


--
Zani
(if I have posted here, I really am stuck!)



"JakeyC" wrote:

If it's set up as I think you describe (a spreadsheet containing all
the info for a record number along that same row), then simply reverse
the process.

When you write the data to the user form, you've probably got a button
that runs a macro to transfer the data to the cells from your textboxes
such as

Sheets("Data").Cells(14,37).Value = UserForm1.TextBox1.Text (or
similar).

To get the data back to the userform, just reverse the statement and
place it in a suitable event module:

UserForm1.TextBox1.Text = Sheets("Data").Cells(14,37).Value

I also have a slight problem returning times from the data in the spreadsheet
back to the userform - they show as numbers rather than the original 24 hr
clock.
--
Zani
(if I have posted here, I really am stuck!)



"JakeyC" wrote:

If it's set up as I think you describe (a spreadsheet containing all
the info for a record number along that same row), then simply reverse
the process.

When you write the data to the user form, you've probably got a button
that runs a macro to transfer the data to the cells from your textboxes
such as

Sheets("Data").Cells(14,37).Value = UserForm1.TextBox1.Text (or
similar).

To get the data back to the userform, just reverse the statement and
place it in a suitable event module:

UserForm1.TextBox1.Text = Sheets("Data").Cells(14,37).Value

Zani,
You can use FORMAT to get the data correct. In the xample below, I
ssume Range("A1") to contain time in format hh:mm e.g. 12:30 in textbox

Private Sub UserForm_Initialize()
TextBox1.Value = Format(ThisWorkbook.Sheets("Sheet1").Range("a1"), "HH:MM")
End Sub

I I understand your requirement about positioning the listox(es), you could
store the value of LISTINDEX for each box and in the UserForm_Initialize
module set LISTINDEX to stored value.

Private Sub UserForm_Initialize()
TextBox1.Value = Format(ThisWorkbook.Sheets("Sheet1").Range("a1"), "HH:MM")
ListBox1.ListIndex = Range("C1") <=== Set ListIndex
End Sub

Private Sub ListBox1_Click()
Range("C1") = ListBox1.ListIndex <== Saved in C1
End Sub

HTH

"Zani" wrote:

I also have a slight problem returning times from the data in the spreadsheet
back to the userform - they show as numbers rather than the original 24 hr
clock.
--
Zani
(if I have posted here, I really am stuck!)



"JakeyC" wrote:

If it's set up as I think you describe (a spreadsheet containing all
the info for a record number along that same row), then simply reverse
the process.

When you write the data to the user form, you've probably got a button
that runs a macro to transfer the data to the cells from your textboxes
such as

Sheets("Data").Cells(14,37).Value = UserForm1.TextBox1.Text (or
similar).

To get the data back to the userform, just reverse the statement and
place it in a suitable event module:

UserForm1.TextBox1.Text = Sheets("Data").Cells(14,37).Value

To select an entry in the listbox, use something like:

ListBox1.Value = Range("B1").Value 'where B1 is the cell you stored the
previous selection in. This might activate the Click event so turn
Events off if need be with Application.EnableEvents = False.

As for the dates, the only workaround I can think of is to store them
as text format in the destination cells.

Thanks I'll give them a go!
--
Zani
(if I have posted here, I really am stuck!)



"JakeyC" wrote:

To select an entry in the listbox, use something like:

ListBox1.Value = Range("B1").Value 'where B1 is the cell you stored the
previous selection in. This might activate the Click event so turn
Events off if need be with Application.EnableEvents = False.

As for the dates, the only workaround I can think of is to store them
as text format in the destination cells.