Opening a Userform by clicking into a Text Form Field

[illogic]

Hello,

i am currently trying to create a userform which opens when a certain text form field is activated.
So instead of being able to type into this field, i want it to open a userform, where option fields are to be selected.
As soon as the selection is done and confirmed, i want the userform to write everthing that was selected into that text form field.

Is it somehow possible to do this? and if so, how can i achieve something like this?

I hope i am not writing nonsense and everyone understands what i am trying to achieve, english is not my native language.


greetings

Manuel

[gmaxey]

Set a macro to run on entry when you enter the formfield. Use that macro to open display the userform. Use a command button click event in the userform to write to the formfield.

[illogic]

Ok, i got the Userform to show up when i click into the formfield. But now, the troubles begin. =)

Here is the code that i wrote so far:

In "This Document":

Sub frmPSAShow()
frmPSA.Show
Unload frmPSA

End Sub

Then i right clicked the formfield and chose frmPSA.show as an entry macro. Works

The code for the Userform contains the following:

Option Explicit

Dim opt1 As Boolean
Dim opt2 As Boolean
Dim opt3 As Boolean
Dim opt4 As Boolean
Dim opt5 As Boolean
Dim opt6 As Boolean
Dim opt7 As Boolean
Dim opt8 As Boolean
Dim opt9 As Boolean
Dim opt10 As Boolean

Private Sub UserForm_Activate()
Me.OptionButton1 = opt1
Me.OptionButton2 = opt2
Me.OptionButton3 = opt3
Me.OptionButton4 = opt4
Me.OptionButton5 = opt5
Me.OptionButton6 = opt6
Me.OptionButton7 = opt7
Me.OptionButton8 = opt8
Me.OptionButton9 = opt9
Me.OptionButton10 = opt10
Me.cmdOK = cmdOK
Me.cmdESC = cmdESC
End Sub

Private Sub cmdOk_Click()

    If OptionButton1.Value = True Then
        ActiveDocument.FormFields("PSA").Result = OptionButton1.Caption
    End If
    
    If OptionButton2.Value = True Then
        ActiveDocument.FormFields("PSA").Result = OptionButton2.Caption
    End If
    
    If OptionButton3.Value = True Then
        ActiveDocument.FormFields("PSA").Result = OptionButton3.Caption
    End If
    
    If OptionButton4.Value = True Then
        ActiveDocument.FormFields("PSA").Result = OptionButton4.Caption
    End If
    
    Unload Me
    'ActiveDocument.FormFields("Gefrp").Select
End Sub

Private Sub cmdESC_Click()
  Unload Me
  'ActiveDocument.FormFields("Gefrp").Select
End Sub

Ok, the problem with this code is, when i activate the option buttons, lets say button 1,2 and 3 and hit OK, only the caption of button 3 will be written into the formfield.
the other captions will be overwritten.

My question is, how can i adjust that code, so that it writes all activated captions into the formfield and seperates them with a comma.
I hope it is clear enough what i am trying to do.

I am very new to programming, so if this code is garbage, i would appreciate any tips to optimize it, so that it functions faster and more efficiently.

[gmaxey]

Not garbage but I have no idea what made you think you need all of those variables:

'In ThisDocument or a Standard Module
Sub frmPSAShow()
Dim oFrm As frmPSA
  Set oFrm = New frmPSA
  oFrm.Show
  Unload frmPSA
  Set oFrm = Nothing
lbl_Exit:
  Exit Sub
End Sub
'In the form module.
Private Sub cmdOk_Click()
Dim strOutput As String
Dim lngIndex As Long
  For lngIndex = 1 To 10
    If Me.Controls("OptionButton" & lngIndex) Then
      strOutput = strOutput & Controls("OptionButton" & lngIndex).Caption & ", "
    End If
  Next
  If Right(strOutput, 2) = ", " Then strOutput = Left(strOutput, Len(strOutput) - 2)
  ActiveDocument.FormFields("PSA").Result = strOutput
  Hide
lbl_Exit:
  Exit Sub
End Sub

[illogic]

Hey,
thank you for helping me out on this. Your code works flawlessly. Now i can learn from it and improve myself.
Yes you are right, there was unnecessary code involved. But i am still learning alot. =)

greets

Manuel

[illogic]

Hello,

i have another question.
Is it somehow possible to jump out of a formfield?
When i close the userform by confirming the selection, the formfield remains active and the content that is written in it can be altered.
I am looking for a way to exit the formfield or cancel the selection.
The only way i found to prevent the content to be altered is to jump into another formfield.

[gmaxey]

Didn't you sort of answer your own question? Other than what you are already doing (jumping out of the formfield), there isn't really a clean solution. You could set the field.enabled property to false which kicks you out of the field, but then you are kicked out permanently and can't go back later to edit the field unless you use the dialog, or some other event to re-enable the field.