Call UserForm VBA Excel

Resolved · High Priority · Version 2007

Thu 2 Jun 2011, 11:38 Delegate Chris said...

Chris has attended:
Excel VBA Intro Intermediate course

Call UserForm VBA Excel

I have a module set up to do something (conditional formatting in this case, though other things will follow) and it's saved in my Personal file. I have created a UserForm so that the scope of the formatting can be described, and that is also saved in my Personal file. I don't want this for a specific document, but for any random document where I want to do this thing. This is not just for me but for my non-techie colleagues, who are going to be getting a copy of my Personal file to run macros without understanding the first thing about VBA.

How do I get the UserForm to appear when the module is run, and then call up the rest of the module, without adding in a dummy module that will confuse my colleagues? An ActiveX control is no good because it needs to be available to all workbooks.

Wed 8 Jun 2011, 09:33 Trainer Stephen said...

RE: Call UserForm VBA Excel

Hi Chris

Thanks for you question

The best bet would be to create the form in your personal macro workbook. This would then make it available to all files open on your workbook.
You would need to make it visible in the VBE's project explorer. As this is rather involved I have found a link to a document that explains the procedure.
If you have any further difficulties please feel free to get back to me
http://www.rondebruin.nl/personal.htm

Regards

Stephen

Wed 8 Jun 2011, 09:54 Delegate Chris said...

RE: Call UserForm VBA Excel

Hi Stephen, thanks for getting back to me.

I had created it in my Personal macro workbook already. (I even found some cool code that auto-closes the Personal macro workbook for me when I close Excel!) But only subs seem to appear as available options, not userforms.

I think what I need is a sub that calls up the userform which then populates and calls up a private sub to do the work. I already have the last two parts, what code would I need for the first part, the initial sub?

Alternatively, I need to know how users can call up the userform without going into the Alt+F11 code. (As ever, I am trying to create idiot-proof macros for my colleagues to use.)

Chris

Wed 8 Jun 2011, 10:29 Trainer Stephen said...

RE: Call UserForm VBA Excel

Hi Chris

I have just created a user form in the personal macro workbook. In addition in that workbook I created a procedure to show the form.
I was then able to run that procedure as a macro in a random workbook and the form opened OK. Not sure if this solves your problem, but it seems to work

Cheers

Stephen

Wed 8 Jun 2011, 10:38 Delegate Chris said...

RE: Call UserForm VBA Excel

Hi Stephen, how did you create the procedure to show the form? What is the code for that?
Chris

Wed 8 Jun 2011, 10:45 Trainer Stephen said...

RE: Call UserForm VBA Excel

Hi Chris

If the form was called MyForm. Then simply

[code]MyForm.show[/Code]

If that is written in a public procedure in your personal macro workbook than it can be seen from within any open workbook as a macro

Cheers

Stephen

Wed 8 Jun 2011, 11:04 Edited on Wed 8 Jun 2011, 11:05 Delegate Chris said...

RE: Call UserForm VBA Excel

Still not working :( Here is my code, first the Subs:

Option Explicit

Public intCondFormStartRow

Public intCondFormEndRow

Public intCondFormStartCol

Public intCondFormEndCol

Public intCondFormRowHght

Public intCondFormRowStep

Public intCondFormColWdth

Public intCondFormColStep



Sub CallfrmCondForm()



frmCondForm.Show



End Sub



Sub CondForm3Clr()



Dim intColC As Integer

Dim intRowC As Integer



For intRowC = intCondFormStartRow To intCondFormEndRow Step intCondFormRowStep

    For intColC = intCondFormStartCol To intCondFormEndCol Step intCondFormColStep

        With Range(Cells(intRowC, intColC), Cells(intRowC + intCondFormRowHght, _

          intColC + intCondFormColWdth))

            .FormatConditions.AddColorScale ColorScaleType:=3

            With .FormatConditions(1)

                With .ColorScaleCriteria(1)

                    .Type = xlConditionValueLowestValue

                    With .FormatColor

                        .Color = 7039480

                        .TintAndShade = 0

                    End With

                End With

                With .ColorScaleCriteria(2)

                    .Type = xlConditionValuePercentile

                    .Value = 50

                    With .FormatColor

                        .Color = 8711167

                        .TintAndShade = 0

                    End With

                End With

                With ColorScaleCriteria(3)

                    .Type = xlConditionValueHighestValue

                    With .FormatColor

                        .Color = 8109667

                        .TintAndShade = 0

                    End With

                End With

            End With

        End With

    Next intColC

Next intRowC



End Sub

And the Userform:

Option Explicit



Private Sub txbStartRow_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbEndRow_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbStartCol_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbEndCol_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbRowHght_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbRowStep_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbColWdth_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub txbColStep_KeyDown(ByVal KeyCode _

As MSForms.ReturnInteger, ByVal Shift As Integer)



    If (Not ((KeyCode >= 48 And KeyCode <= 57) Or (KeyCode >= 96 _

      And KeyCode <= 105))) Or Shift > 0 Then

        KeyCode = 0

        MsgBox "Please enter an integer"

    End If



End Sub



Private Sub cmdRun_Click()



    Unload Me

    Application.ScreenUpdating = False

    

    intCondFormStartRow = txbStartRow

    intCondFormEndRow = txbEndRow

    intCondFormStartCol = txbStartCol

    intCondFormEndCol = txbEndCol

    intCondFormRowHght = txbRowHght

    intCondFormRowStep = txbRowStep

    intCondFormColWdth = txbColWdth

    intCondFormColStep = txbColStep

    

    Call CondForm3Clr



End Sub