Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / August 2007

Tip: Looking for answers? Try searching our database.

Is Multi click Event possible?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ayo - 30 Aug 2007 23:56 GMT
I have 17 CommandButtons, "Browse..." on a form.
Each one of them is suppose to open the FileOpen Dialog at a specific folder
After selecting a file, the file is to be assign to the TextBox
corresponding to each button
A few of them open to the same folder location

My question is, is it possible to write a code that doesn't require me
writing a click event for each Browse button?
And if there is such a code I would appreciate any help to point me in the
right direction.

Thanks
Bob Phillips - 31 Aug 2007 00:06 GMT
In the userform, add this code

Option Explicit

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cBtnEvents As clsUserFormEvents
Dim ctl As msforms.Control

   Set mcolEvents = New Collection

   For Each ctl In Me.Controls
       If TypeName(ctl) = "CommandButton" Then
           Set cBtnEvents = New clsUserFormEvents
           Set cBtnEvents.mButtonGroup = ctl
           mcolEvents.Add cBtnEvents
       End If
   Next

End Sub

And create a Class module (call it clsUserFormEvents) module, with this code

Option Explicit

Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
   MsgBox mButtonGroup.Caption & " has been pressed"
End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have 17 CommandButtons, "Browse..." on a form.
> Each one of them is suppose to open the FileOpen Dialog at a specific
[quoted text clipped - 9 lines]
>
> Thanks
Ayo - 31 Aug 2007 04:36 GMT
Thanks Bob. This is very helpful. I think I can make this work. I am very
grateful.

> In the userform, add this code
>
[quoted text clipped - 41 lines]
> >
> > Thanks
Ayo - 31 Aug 2007 05:26 GMT
Hey Bob,
 Thanks again. I got that to work with the form that I have but I don't
know how to get to the next stage. Instead of showing a message box I want it
to open the Open File Dialog to a specific folder. Do I have to creat another
class for that? I am lost again. Help, please.

Ayo

> In the userform, add this code
>
[quoted text clipped - 41 lines]
> >
> > Thanks
Bob Phillips - 31 Aug 2007 11:11 GMT
No, just change my throw-away example code, the MsgBox, with your real code,
the FileDialog.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hey Bob,
>  Thanks again. I got that to work with the form that I have but I don't
[quoted text clipped - 53 lines]
>> >
>> > Thanks
Ayo - 31 Aug 2007 22:42 GMT
Thanks for everything so far Bob, but I am going to ask for your help once
more if you don't mind. It seems I take 5 steps forward and three steps back.
It looked like I got it to work the way it should then all of a sudden, it's
not working any more. I guess I need someone else to look the code over for
me and maybe see what I don't see. I would reaally appreciate it if you can
look at this and maybe tell me what I am doing wrong.

Thanks
      Ayo

Option Explicit

Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
Dim btn_num As String
   'MsgBox mButtonGroup.Caption & " has been pressed"
   btn_num = Right(mButtonGroup.Name, Len(mButtonGroup.Name) - 6)
   assignFiles btn_num
End Sub

Sub assignFiles(btn_num As String)
Dim getFolder As String
Dim fl As FileDialog
Dim ctl As Control
Dim vrtSelectedItem As Variant

Set fl = Application.FileDialog(msoFileDialogFilePicker)   '.InitialFileName

   With fl
       For Each ctl In frmOpenFiles.Controls
           If TypeName(ctl) = "TextBox" Then
               If Len(ctl.Name) = 6 Then
                   If Right(ctl.Name, 1) = btn_num Then GoTo Get_File
                       
               ElseIf Len(ctl.Name) = 7 Then
                   If Right(ctl.Name, 2) = btn_num Then GoTo Get_File
                   
               End If
           End If
           Exit Sub
       Next ctl
Get_File:
       .InitialFileName = ctl.Value
       If .Show = -1 Then
           For Each vrtSelectedItem In .SelectedItems
              getFolder = vrtSelectedItem
           Next vrtSelectedItem
       End If
       If getFolder = Empty Then
           MsgBox "You Must select a file to continue. Please Try Again."
           Exit Sub
       ElseIf getFolder <> Empty Then
           ctl.Text = getFolder
       End If
   End With
   
End Sub

> No, just change my throw-away example code, the MsgBox, with your real code,
> the FileDialog.
[quoted text clipped - 56 lines]
> >> >
> >> > Thanks
JMB - 31 Aug 2007 00:24 GMT
You could put most of the code in a separate macro or function and have the
command buttons call the macro/function (passing it the folder to open and
the textbox control in which the filename s/b placed).  

But you would still need a click event for each button.  

> I have 17 CommandButtons, "Browse..." on a form.
> Each one of them is suppose to open the FileOpen Dialog at a specific folder
[quoted text clipped - 8 lines]
>  
> Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.