Results 1 to 12 of 12

Thread: Office 2007 Excel Vba - Dynamic UserForm

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Office 2007 Excel Vba - Dynamic UserForm

    Hi,

    I would like to check if there are any dynamic userform for excel 2007?

    Current i have four different worksheet (East, South, West North) which contain client information. I have a userform and i can select which user form to filter the data. It work perfectly for the attachment file below

    I would like to improve it.
    If let say i would like to add one more factor to filter which is adding some places in the four different worksheet (East, South, West North)

    Form Example:
    East (Hongkong, China, Japan, Philipines)
    South (Singapore, Malaysia, Thailand, Indonesia)
    West (England, Germary, Italy, Spain)
    North (North Korean, Russia)

    You may refer below image
    Do you think is it possible? Or any suggestion would be appreciated
    Attached Images Attached Images    
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel Vba - Dynamic UserForm

    that seems quite simple, where do you store your list, for the countries in each sheet?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Hi, thanks for your reply. I store my lists (Country) in the four different sheet (East, South, West North)

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Any advise or suggest for this would be appreciated

  5. #5
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Do you really need a UserForm displayed in which none of the hemispheres (N, S ,E or W) is selected?
    In this case you need to rearrange all the controls on the Userform if the selection of the hemisphere (N, S ,E or W) is changed, to do that I'd make a sub that places all controls based on the actual selection.
    Place each control relative to the previous one instead of using hard-coded .Left and .Top properties!

    For all the different hemisphere selections I would use the same Frame, changing only the .text properties of the Checkboxes. A selection of the first Checkbox would need the selcted hemisphere to know which country is relly selected.
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    I wish I can show it at the same userform. When the (e,s,w,n) is selected. Can you roughtly show me the code?

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Or do you think if it is better to have a userform which have 4 buttons to call the different userform for (E, S, W, N)?

  8. #8
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Office 2007 Excel Vba - Dynamic UserForm

    I did an Update on your Userform in order to show the Locations Frame (Frame4).
    The code to show filtered data based on a locations selection is NOT implemented, you should be able to do that by yourself!
    Attached Files Attached Files
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    I will try the file that you sent to me. Thanks

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Code:
     
    Select Case Location
            Case Is = "East"
                OptionButton7.Caption = "Hongkong"
                OptionButton8.Caption = "China"
                OptionButton9.Caption = "Japan"
                OptionButton10.Caption = "Philipines"
            Case Is = "West"
                OptionButton7.Caption = "England"
                OptionButton8.Caption = "Germary"
                OptionButton9.Caption = "Italy"
                OptionButton10.Caption = "Spain"
            Case Is = "North"
                OptionButton7.Caption = "North Korean"
                OptionButton8.Caption = "Russia"
                OptionButton9.Caption = "??"
                OptionButton10.Caption = "??"
      
            Case Is = "South"
                OptionButton7.Caption = "Singapore"
                OptionButton8.Caption = "Malaysia"
                OptionButton9.Caption = "Thailand"
                OptionButton10.Caption = "Indonesia"
        End Select
    if i only have two option under North, after i remove the below code,
    Code:
    OptionButton9.Caption = "??"
    OptionButton10.Caption = "??"
    it show me the option for Italy and Spain

    Is there anyway to show only two option for north,
    I try to replace the above code with
    Code:
    OptionButton9.Visible = False
    OptionButton10.Visible = False
    But it is not working if i change the option after selecting North, it will turn off the option9 and option 10

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jul 2007
    Posts
    139

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Do you think should i create four different frames in order to have different number of selections inside the frame?

    East, West, South, i have four places to choose and North i only have 2 places to choose.

  12. #12
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Office 2007 Excel Vba - Dynamic UserForm

    Don't create anotgher Frame!
    Follow the way with using "OptionButton9.Visible = False", since you are setting the .Visible-Property to False in case North is selcted, you need to set it to True again whenver you want to see it!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width