98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Call UserForm VBA Excel
Call UserForm VBA Excel
Resolved · High Priority · Version 2007
Thu 2 Jun 2011, 11:38Delegate 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:33Trainer 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:54Delegate 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:29Trainer 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:38Delegate 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:45Trainer 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:05Delegate 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 SubAnd 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 SubIt works fine until the second Sub starts.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Moving between Worksheets without using the mouseUse the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys. |
