call userform vba excel
Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Call UserForm VBA Excel

Call UserForm VBA Excel

· High Priority · Version 2007

Thu 2 Jun 2011, 11:38 replyReply 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.

For upcoming training course dates see: Pricing & availability

Wed 8 Jun 2011, 09:33 replyReply 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 replyReply 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 replyReply 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 replyReply 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 replyReply Trainer Stephen said...

RE: Call UserForm VBA Excel

Hi Chris

If the form was called MyForm. Then simply

MyForm.show

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 replyReply 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


It works fine until the second Sub starts.

 

Please browse our web site to find out more about
microsoft+excel+training+course and other Microsoft training courses.

Excel tip:

Viewing two Excel 2010 Workbooks at the same time

Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!

1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side

View all Excel hints and tips



MS Project training
Course rating:
4.8 stars - based on 5149 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Facebook · Twitter · Google+ · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom