Creating a userform that lets me select the Client I want?

[tonywatsonhelp]

Hi Everyone,
I want to make a userform that fills in the number of clients i have and lets me choose which one i want to use?

So I can make the userform pop up etc. so lets just concentrat on what i want
I have 10 text boxes linked to 10 cells, if any cell is empty I want that text box to be hidden
so
I want a bit of code that says if Cell A10 is "" then text box 10 Not visible

if you can help me with this that would be great

Thank you

Tony

 

[MickG]

Re: Help Creating a userform that lets me select the Client I want?

This code will work when you first show the userform and will also work when the userform is showing and you delete or show values in the Controlsource Cells in the Worksheets.


Try something like in sheet Module:-

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
UserForm1.Show vbModeless
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    Call UserForm1.test(Target)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Paste this in Userform Module (At top of module):-

Option Explicit
[COLOR="Navy"]Sub[/COLOR] test(r [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Ctrl [COLOR="Navy"]As[/COLOR] Control, t
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ctrl [COLOR="Navy"]In[/COLOR] Me.Controls
   [COLOR="Navy"]If[/COLOR] TypeName(Ctrl) = "TextBox" [COLOR="Navy"]Then[/COLOR]
     [COLOR="Navy"]If[/COLOR] UCase(Ctrl.ControlSource) = UCase(r.Address(0, 0)) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Ctrl.Value = "" [COLOR="Navy"]Then[/COLOR]
           Ctrl.Visible = False
        [COLOR="Navy"]Else[/COLOR]
            Ctrl.Visible = True
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ctrl
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Ctrl [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ctrl [COLOR="Navy"]In[/COLOR] Me.Controls
    [COLOR="Navy"]If[/COLOR] TypeName(Ctrl) = "TextBox" [COLOR="Navy"]Then[/COLOR]
        Ctrl.Visible = True
            [COLOR="Navy"]If[/COLOR] Ctrl.Value = "" [COLOR="Navy"]Then[/COLOR]
                Ctrl.Visible = False
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ctrl
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[tonywatsonhelp]

Re: Help Creating a userform that lets me select the Client I want?

Wow, thank you this is excellent.
thank you very much

Tony

 

[MickG]

Re: Help Creating a userform that lets me select the Client I want?

You're welcome