+ Reply to Thread
Results 1 to 3 of 3

Type Mismatch-calculating textbox values from different multipage tabs

Hybrid View

cschoyer Type Mismatch-calculating... 06-06-2012, 03:04 PM
protonLeah Re: Type Mismatch-calculating... 06-06-2012, 03:19 PM
cschoyer Re: Type Mismatch-calculating... 06-06-2012, 04:52 PM
  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Type Mismatch-calculating textbox values from different multipage tabs

    I'm sure this is easy for some of you, but I just can't figure it out! I keep getting a type mismatch when I try to calculate the product of two text boxes, each from a different tab on a multipage userform. Here's my code:

    'format percentage boxes
    Private Sub Allocation1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If Allocation1.Text <> "" Then
            Me.Allocation1.Value = Format(Me.Allocation1 / 100, "0.%")
        End If
        On Error Resume Next
    End Sub
    
    Private Sub Allocation2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If Allocation2.Text <> "" Then
            Me.Allocation2.Value = Format(Me.Allocation2 / 100, "0.%")
        End If
        On Error Resume Next
    End Sub
    
    'for multiple locations, enter new row with allocation and location
    Private Sub EnterButton_Click()
    
    With Worksheets("INVOICES")
        Dim JSID As String
        Dim Amt As Double
        
        Dim A As Integer
        Dim B As Integer
    
    If Allocation1.Value <> "" Then
        A = Format(Me.Allocation1.Value, "0.")
    End If
    
    If Allocation2.Value <> "" Then
        B = Format(Me.Allocation2.Value, "0.")
    End If
    
    If A + B <> 100 Then
        MsgBox "Allocation entries must total 100%."
        Exit Sub
    End If
        
        JSID = UserForm1.MultiPage1.Pages("InvoiceInfo").IDNumberBox.Value
        Amt = UserForm1.MultiPage1.Pages("InvoiceInfo").AmountBox.Value
        
    'enter new row for allocation1
        If IsNumeric(IDNumberBox) Then
            Newrow = Application.WorksheetFunction.Match(CLng(JSID), Range("B:B"), 0)
            Rows(Newrow).Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            Selection.Font.Italic = True
        Else
            Newrow = Application.WorksheetFunction.Match(JSID, Range("B:B"), 0)
            Rows(Newrow).Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            Selection.Font.Italic = True
        End If
    
        Cells(Newrow, 7) = Amt * Allocation1
    
    'enter new row for allocation2 and location2
        If IsNumeric(IDNumberBox) Then
            Newrow = Application.WorksheetFunction.Match(CLng(JSID), Range("B:B"), 0)
            Rows(Newrow).Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            Selection.Font.Italic = True
        Else
            Newrow = Application.WorksheetFunction.Match(JSID, Range("B:B"), 0)
            Rows(Newrow).Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            Selection.Font.Italic = True
        End If
    
        Cells(Newrow, 7) = Amt * Allocation2
    
    End If
       End With
    End Sub
    Where allocations 1 and 2 are percentages (%) entered in on one multipage sheet, and the amount ($) is on another multipage sheet (named InvoiceInfo) of the same userform. I want to get the product of these, but I keep getting type mismatch error! Please help! Many many thanks in advance!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    13,037

    Re: Type Mismatch-calculating textbox values from different multipage tabs

    Can you post a workbook with some numbers I can step through and test?
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Type Mismatch-calculating textbox values from different multipage tabs

    inv appr xl help.xlsm



    I have commented out some of the things that aren't working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1