user form, saving values

[ophelia]

I have a user form, which is basically a collection of combo boxes.
What I want, is basically, for the values chosen by the user to b
saved, so the next person who opens the user form has the value
displayed that the last person chose.

So for instance, user 1. launches the user form, picks cbo1 = red
cbo2=green cbo3=blue

user 2 goes into the user form sometimel later, and sees cbo1 = red
cbo2=green cbo3=blue and can then edit cbo1=blue

user 3 would then go into teh user form and see cbo1=blue, etc etc.

This can really be done through any means necessary, and I generall
don't know if it's even possible.
I was thinking, of maybe having some sort of command button which woul
"save" the cbo selections.

Can anyone shed some light on this?

Thank you!!

 

[Bob Phillips]

I would have a hidden worksheet, and in the Userform_Terminate event save
the values to cells on that sheet. Then on the Userform_Activate event, read
them back in

Private Sub UserForm_Activate()
With Me
If Worksheets("UFValues").Range("A1").Value <> "" Then
ComboBox1.Value = Worksheets("UFValues").Range("A1").Value
End If
If Worksheets("UFValues").Range("A2").Value <> "" Then
ComboBox2.Value = Worksheets("UFValues").Range("A2").Value
End If
If Worksheets("UFValues").Range("A3").Value <> "" Then
TextBox1.Value = Worksheets("UFValues").Range("A3").Value
End If
End With
End Sub

Private Sub UserForm_Terminate()
With Me
Worksheets("UFValues").Range("A1").Value = ComboBox1.Value
Worksheets("UFValues").Range("A2").Value = ComboBox2.Value
Worksheets("UFValues").Range("A3").Value = TextBox1.Value
End With
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[paul.robinson]

Hi
You can store the values in a name.
e.g. suppose you have 3 textboxes then this code would go into the
"Submit" button for your form:

Dim tbArray(1 To 3) As String
With Me
tbArray(1) = .tb1.Value
tbArray(2) = .tb2.Value
tbArray(3) = .tb3.Value
End With

Activeworkbook.Names.Add Name:="tbValues", RefersTo:=tbArray,
Visible:=False

To access these values later (workbook can be closed in the meantime)
put this code in the Userform_Initialize sub for the Userform;

Dim tbStart as Variant
tbStart = [tbValues]
With Me
.tb1.Value = tbStart(1)
.tb2.Value = tbStart(2)
.tb3.Value = tbStart(3)
End With

Note that tbStart is a 1 based array (starts at 1), regarless of
whether Option Base 0 is used.

regards
Paul

 

[ophelia]

Hello,
Thanks for your replies! I'm trying this method at the moment, but
seem to have an issue, if I change the value in cbo3, then when I clos
the form and reopen it, it saves the value in cbo1.
I'm not sure why at all!
But if this works for the correct cbos, it's exactly what I want
thanks!