Couple of userform textbox/commanbutton questions

[PATSYS]

Hi all,

I have textbox1 and commandbutton3 in userform.

Commandbutton3 has the below code:

Private Sub CommandButton3_Click()
x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose File", MultiSelect:=False)
MsgBox "You selected " & x
End Sub
[code]


1. I would like that commandbutton3 to be only visible if the user selects textbox1

2. When user clicks on commandbutton3 and selects the file, I would like x to populate textbox1.


Is that possible?

Thanks

 

[mikerickson]

You might try replacing the text box with a combo box.
Setting the .ShowDropButtonWhen property to fmShowDropButtonWhenFocus will show the drop button only when the box gets the focus.
Then the DropButt******* event can call GetOpenFileName.

Private Sub ComboBox1_DropButt*******()
    Dim uiFile As String
    uiFile = Application.GetOpenFilename
    If uiFile <> "False" Then
        ComboBox1.Text = uiFile
    End If
    TextBox1.SetFocus:Rem move to next control in tab order
End Sub

Why is the site's editor replacing "o n C l i c k" (no spaces) with ****** ?

 

[Jon von der Heyden]

Hi Mike

I was planning on suggesting that with a text box but I can't figure out how to set the DropButtonStyle. According to my excel 2003 VBA helpfile it is a valid property for a text box but then it doesn't actually seem to change the style (although I don't encounter any error). Do you know what that is about? Combobox seems fine.

 

[mikerickson]

In my Excel 2004, the DropButton Click event is listed for a text box, but no DropButtonStyle property.

My guess is an error in the Help system.

 

[Kenneth Hobson]

Private Sub CommandButton3_Click()
  Dim x
  x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose File", MultiSelect:=False)
  OKBttn.SetFocus
  If x = False Then Exit Sub
  TextBox1.Value = x
End Sub

Private Sub OKBttn_Click()
  Unload Me
End Sub

Private Sub OKBttn_Enter()
  CommandButton3.Visible = False
End Sub

Private Sub TextBox1_Enter()
  CommandButton3.Visible = True
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  On Error Resume Next
  CommandButton3.Visible = False
End Sub

Private Sub UserForm_Click()
  If ActiveControl.Name <> TextBox1.Name Then CommandButton3.Visible = False
End Sub

 

[mikerickson]

My guess was wrong, cause this works. Wow, textboxes' got drop buttons!

Private Sub UserForm_Initialize()
    With TextBox1
        .ShowDropButtonWhen = fmShowDropButtonWhenFocus
        .DropButtonStyle = fmDropButtonStylePlain
    End With
End Sub

Private Sub TextBox1_DropButt*******()
    Dim uiFile As String
    uiFile = Application.GetOpenFilename
    If uiFile <> "False" Then
        TextBox1.Text = uiFile
    End If
End Sub

(And Mr.Excel's editor still doesn't like "o n C l i c k")

 

[Jon von der Heyden]

This is the bit I don't get. No help available:

.ShowDropButtonWhen = fmShowDropButtonWhenFocus

Where are these listed?

 

[mikerickson]

In the Object Browser there is a class fmDropButtonStyle that lists
fmDropButtonStyleArrow
fmDropButtonStyleEllipsis
fmDropButtonStylePlain
fmDropButtonStyleReduce

 

[PATSYS]

Private Sub CommandButton3_Click()
  Dim x
  x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose File", MultiSelect:=False)
  OKBttn.SetFocus
  If x = False Then Exit Sub
  TextBox1.Value = x
End Sub

Private Sub OKBttn_Click()
  Unload Me
End Sub

Private Sub OKBttn_Enter()
  CommandButton3.Visible = False
End Sub

Private Sub TextBox1_Enter()
  CommandButton3.Visible = True
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  On Error Resume Next
  CommandButton3.Visible = False
End Sub

Private Sub UserForm_Click()
  If ActiveControl.Name <> TextBox1.Name Then CommandButton3.Visible = False
End Sub

Hi Kenneth,

I keep getting error in the line:

OKBttn.SetFocus

Would you know why?

Thanks

 

[PATSYS]

My guess was wrong, cause this works. Wow, textboxes' got drop buttons!

Private Sub UserForm_Initialize()
    With TextBox1
        .ShowDropButtonWhen = fmShowDropButtonWhenFocus
        .DropButtonStyle = fmDropButtonStylePlain
    End With
End Sub

Private Sub TextBox1_DropButt*******()
    Dim uiFile As String
    uiFile = Application.GetOpenFilename
    If uiFile <> "False" Then
        TextBox1.Text = uiFile
    End If
End Sub

(And Mr.Excel's editor still doesn't like "o n C l i c k")

Hi Mike,

Sorry I am a bit new to userform. Where do I paste these codes?

Thanks