Modeless Userform using .Show vbModeless started triggering Type Mismatch Error 13

T

tjdurkin

New Member
Joined
Apr 28, 2011
Messages
21
Hello all,

Thanks in advance for any suggestions!

A little background: I've been tinkering with VBA for 3-4 years now starting with VBA for Dummies, moving on to Mr Walkenbach's Excel 2007:Power Programming with VBA, and have just recently gotten into Professional Excel Development: The definitive guide to developing applications using MS Excel, VBA, and .NET (2nd Ed. Bovey, Wallentin, Bullen, Green).

UserForm: I have a Multipage form with 3 pages: Billings, Collections, and Anticipated Future Provisions. The intent of the Form is to be modeless snapshot of pie charts that are located on Sheets("Analysis"). I hope to have the form display on any sheet as modeless based on user preference. The charts are passed into an Image control on each page of the form by saving each chart as a gif file and loading the gif when the userform initializes.

Code: 
Private Sub UserForm_Initialize()
'Load Pics into Form Pages
Me.MultiPage1.Page1.Image1.Picture = LoadPicture(FnameB)
Me.MultiPage1.Page2.Image2.Picture = LoadPicture(FnameC)
Me.MultiPage1.Page3.Image3.Picture = LoadPicture(FnameP)

'determine Label2 & Label3
Dim BillTot As Long
Dim BillPct As Double
Dim CollTot As Long
Dim CollPct As Double
Dim ProvTot As Variant

    BillTot = Sheets("Analysis").Range("C146").End(xlUp).Value
        BillPct = Sheets("Analysis").Range("B11").Value
    CollTot = Sheets("Analysis").Range("C212").End(xlUp).Value
        CollPct = Sheets("Analysis").Range("F14").Value
    ProvTot = Sheets("Analysis").Range("J71").End(xlUp).Value
        
'MsgBox "BillPct: " & BillPct & vbNewLine & "CollPct: " & CollPct
'Exit Sub

If MultiPage1.SelectedItem.Caption = "Billing" Then
    Me.Label2 = "Billing Goal: " & Format(BillTot, "Currency")
    Me.Label3 = Format(BillPct, "Percent") & " Complete"
    Call qvBillAnalysis
End If

If MultiPage1.SelectedItem.Caption = "Collection" Then
    Me.Label2 = "Collection Goal: " & Format(CollTot, "Currency")
    Me.Label3 = Format(CollPct, "Percent") & " Complete"
    Call qvCollAnalysis
End If

If MultiPage1.SelectedItem.Caption = "Anticipated Future Provisions" Then
    If ProvTot = "Sum of Expected Provision" Then
        Me.Label2 = "No scheduled provisions in next 30 days."
        Me.Label3 = ""
            Call qvCollAnalysis
    Else: Me.Label2 = "30 Day Scheduled Provision Impact: " & Format(ProvTot, "Currency")
        Me.Label3 = ""
            Call qvCollAnalysis
    End If
End If
End Sub
The procedures called when a page is changed brings the user to the Sheets("Analysis") location of the detailed analysis for the pie chart being displayed in the form. For example:
Code: 
Sub qvBillAnalysis()
    Sheets("Analysis").Activate
    Range("A71").Select
    ActiveWindow.ScrollRow = ActiveCell.Row
End Sub
The Sub that shows the user form is the same that saves the charts as gifs and sets their path as FnameB, FnameC, and FnameP. The form is shown as modeless by using the .Show vbModeless.

Code: 
Sub qvBCProgress()
Set BillProgressChart = Sheets("Analysis").ChartObjects(1).Chart
FnameB = ThisWorkbook.Path & "\tempbill.gif"
BillProgressChart.Export Filename:=FnameB, FilterName:="GIF"

Set CollProgressChart = Sheets("Analysis").ChartObjects(2).Chart
FnameC = ThisWorkbook.Path & "\tempcoll.gif"
CollProgressChart.Export Filename:=FnameC, FilterName:="GIF"

FnameP = ThisWorkbook.Path & "\danceparty.gif"
qvBCProgressFRM.Show vbModeless
End Sub
My ShowModal property on the Userform is set to true as I thought I saw a suggestion on the web somewhere that this should be done when using .Show vbModeless. Is this accurate?

I am confused because the file was working beautifully before I added a couple additional pivots and an extra tab in the workbook that holds the source for the added pivot. Randomly, qvBCProgress started throwing the Type Mismatch (13) error and highlighting my .Show vbModeless line and I have not been able to get the UserForm to show since.

Of course, I have backup copies of my file, and I have checked the differences in the coding... the syntax is exact. Any ideas?

Working code (in back-up):
Code: 
Sub qvBCProgress()
Set BillProgressChart = Sheets("Analysis").ChartObjects(1).Chart
FnameB = ThisWorkbook.Path & "\tempbill.gif"
BillProgressChart.Export Filename:=FnameB, FilterName:="GIF"

Set CollProgressChart = Sheets("Analysis").ChartObjects(2).Chart
FnameC = ThisWorkbook.Path & "\tempcoll.gif"
CollProgressChart.Export Filename:=FnameC, FilterName:="GIF"

FnameP = ThisWorkbook.Path & "\danceparty.gif"
qvBCProgressFRM.Show vbModeless
End Sub
Code: 
Private Sub UserForm_Initialize()
'Load Pics into Form Pages
Me.MultiPage1.Page1.Image1.Picture = LoadPicture(FnameB)
Me.MultiPage1.Page2.Image2.Picture = LoadPicture(FnameC)
Me.MultiPage1.Page3.Image3.Picture = LoadPicture(FnameP)

'determine Label2 & Label3
Dim BillTot As Long
Dim BillPct As Double
Dim CollTot As Long
Dim CollPct As Double
Dim ProvTot As Variant

    BillTot = Sheets("Analysis").Range("C146").End(xlUp).Value
        BillPct = Sheets("Analysis").Range("B11").Value
    CollTot = Sheets("Analysis").Range("C212").End(xlUp).Value
        CollPct = Sheets("Analysis").Range("F14").Value
    ProvTot = Sheets("Analysis").Range("J71").End(xlUp).Value
        
'MsgBox "BillPct: " & BillPct & vbNewLine & "CollPct: " & CollPct
'Exit Sub

If MultiPage1.SelectedItem.Caption = "Billing" Then
    Me.Label2 = "Billing Goal: " & Format(BillTot, "Currency")
    Me.Label3 = Format(BillPct, "Percent") & " Complete"
    Call qvBillAnalysis
End If

If MultiPage1.SelectedItem.Caption = "Collection" Then
    Me.Label2 = "Collection Goal: " & Format(CollTot, "Currency")
    Me.Label3 = Format(CollPct, "Percent") & " Complete"
    Call qvCollAnalysis
End If

If MultiPage1.SelectedItem.Caption = "Anticipated Future Provisions" Then
    If ProvTot = "Sum of Expected Provision" Then
        Me.Label2 = "No scheduled provisions in next 30 days."
        Me.Label3 = ""
            Call qvCollAnalysis
    Else: Me.Label2 = "30 Day Scheduled Provision Impact: " & Format(ProvTot, "Currency")
        Me.Label3 = ""
            Call qvCollAnalysis
    End If
End If
End Sub
Finally, the reason i need my form to be modeless is I want it to float as a summary while the user clicks text boxes (with assigned macros that activate the appropriate sheet and cell) to navigate.

Any ideas/thoughts/help is appreciated!!

Not sure if this matters but there is no button on the form to close. I expect the users to click the Windows red X to close the window.

Thanks! TJ
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi TJ and Welcome to the Board,

tjdurkin said:
My ShowModal property on the Userform is set to true as I thought I saw a suggestion on the web somewhere that this should be done when using .Show vbModeless. Is this accurate?
Click to expand...

For a Modeless Userform, the ShowModal property should be set to False.
 
Upvote 0
Thanks JS! That didn't help, but that is because I'm an idiot :LOL:. The form was failing because when I added the new pivot table, I also changed the source data on my BillingPvt. This caused the cell reference that pointed to the BillingPvt to throw a REF error because I never repointed it for the new source (cell reference included GETPIVOTDATA).

Well, at the very least, I've got my first forum post under my belt. Had I been searching a "Spreadsheet Basics" forum instead of assuming my issue was with my VBA code, I probably would have found my answer! :rolleyes:

Thanks Again!
 
Upvote 0
tjdurkin said:
Thanks JS! That didn't help, but that is because I'm an idiot :LOL:. The form was failing because when I added the new pivot table, I also changed the source data on my BillingPvt. This caused the cell reference that pointed to the BillingPvt to throw a REF error because I never repointed it for the new source (cell reference included GETPIVOTDATA).

Well, at the very least, I've got my first forum post under my belt. Had I been searching a "Spreadsheet Basics" forum instead of assuming my issue was with my VBA code, I probably would have found my answer! :rolleyes:

Thanks Again!
Click to expand...

Glad you solved that and it gave you a reason to join the MrExcel Board.

Did you find that you also needed to set the ShowModal property to False?
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Solved
Error Handler invoked but no error number, description or source?
Replies
2
Views
505
AOB
AOB
I
  • Question
Insert charts in userform
Replies
7
Views
3K
inactiveUser214710
I
F
  • Question
Displaying a chart from a worksheet on a userform
Replies
0
Views
569
forneous
F
Guinaba
  • Question
Run time error 481 - Invalid Picture
Replies
5
Views
5K
sureshtrb
S
Jaafar Tribak
  • Question
Zooming on Images (Drawing with GDIPlus + Subclassing with comctl32.dll)
Replies
1
Views
3K
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,220,965
Messages
6,157,120
Members
451,399
Latest member
alchavar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top