Run-time error '13': I created a VBA to open User Form, but not working

K

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
111
Office Version
  1. 365
  2. 2016
VBA Code: 
Option Explicit

Sub Reset()

    Dim iRow As Long

iRow = [Counta(Quotes!B:B] ' identify the last row

With frmForm
.TxtCompanyName.Value = ""
.txtProjectName.Value = ""
.TxtDate.Value = ""
.TxtNo.Value = ""
.TxtQuoteNo.Value = ""
.TxtItemNo.Value = ""
.TxtArea.Value = ""
.TxtDes1.Value = ""
.TxtDes2.Value = ""
.TxtHeight.Value = ""
.TxtLength.Value = ""
.TxtLevels.Value = ""
.TxtErect.Value = ""
.TxtHUI1.Value = ""
.TxtHUI2.Value = ""
.TxtHUI3.Value = ""
.TxtSTD.Value = ""
.TxtLCS.Value = ""
.TxtIB.Value = ""
.txtTMNo.Value = ""
.TxtTM.Value = ""
.TxtHUMNo.Value = ""
.TxtHUM.Value = ""
.TxtDismantle.Value = ""
.TxtTransport.Value = ""
.TxtSCS.Value = ""
.TxtSundry.Value = ""
.TxtSupplyBeams.Value = ""
.TxtOthers.Value = ""
.TxtENG.Value = ""
.TxtHire.Value = ""
.TxtHireWeeks.Value = ""
.TxtOH.Value = ""
.TxtWklyHire.Value = ""
.TxtTotal.Value = ""


.CmbConVar.Clear

.CmbConVar.AddItem "Subcontract"
.CmbConVar.AddItem "Variation"


.lstdatabase.ColumnCount = 33
.lstdatabase.ColumnHeads = True

.lstdatabase.ColumnWidths = "6.14,10.71,4.43,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29,12.29"

If iRow > 1 Then
.lstdatabase.RowSource = "Quotes!B4:AH" & iRow
Else
.lstdatabase.RowSource = "Quotes!B4:AH"
End If


   End With



End Sub


Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Quotes")

iRow = [Counta(Quotes!B:B] + 1

With sh

   .Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = frmForm.TxtDate.Value
.Cells(iRow, 3) = frmForm.TxtNo.Value
.Cells(iRow, 4) = frmForm.CmbConVar.Value
.Cells(iRow, 5) = frmForm.TxtQuoteNo.Value
.Cells(iRow, 6) = frmForm.TxtItemNo.Value
.Cells(iRow, 7) = frmForm.TxtArea.Value
.Cells(iRow, 8) = frmForm.TxtDes1.Value
.Cells(iRow, 9) = frmForm.TxtDes2.Value
.Cells(iRow, 10) = frmForm.TxtHeight.Value
.Cells(iRow, 11) = frmForm.TxtLength.Value
.Cells(iRow, 12) = frmForm.TxtLevels.Value
.Cells(iRow, 13) = frmForm.TxtErect.Value
.Cells(iRow, 14) = frmForm.TxtHUI1.Value
.Cells(iRow, 15) = frmForm.TxtHUI2.Value
.Cells(iRow, 16) = frmForm.TxtHUI3.Value
.Cells(iRow, 17) = frmForm.TxtSTD.Value
.Cells(iRow, 18) = frmForm.TxtLCS.Value
.Cells(iRow, 19) = frmForm.TxtIB.Value
.Cells(iRow, 20) = frmForm.txtTMNo.Value
.Cells(iRow, 21) = frmForm.TxtTM.Value
.Cells(iRow, 22) = frmForm.TxtHUMNo.Value
.Cells(iRow, 23) = frmForm.TxtHUM.Value
.Cells(iRow, 24) = frmForm.TxtDismantle.Value
.Cells(iRow, 25) = frmForm.TxtTransport.Value
.Cells(iRow, 26) = frmForm.TxtSCS.Value
.Cells(iRow, 27) = frmForm.TxtSundry.Value
.Cells(iRow, 28) = frmForm.TxtSupplyBeams.Value
.Cells(iRow, 29) = frmForm.TxtOthers.Value
.Cells(iRow, 30) = frmForm.TxtENG.Value
.Cells(iRow, 31) = frmForm.TxtHire.Value
.Cells(iRow, 32) = frmForm.TxtHireWeeks.Value
.Cells(iRow, 33) = frmForm.TxtOH.Value
.Cells(iRow, 34) = frmForm.TxtWklyHire.Value
.Cells(iRow, 35) = frmForm.TxtTotal.Value
.Cells(iRow, 35) = Application.UserName
   .Cells(iRow, 35) = [text(Now(),"DD-MM-YY HH:MM:SS")]

End With



End Sub


Sub Show_Form()

    frmForm.Show

End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Change this line from the Reset macro...

VBA Code: 
iRow = [Counta(Quotes!B:B]

to this:

VBA Code: 
Sheets("Quotes").Cells(Rows.Count, "B").End(xlUp).Row

Then change this line from the Submit macro...

VBA Code: 
iRow = [Counta(Quotes!B:B] + 1

to this:

VBA Code: 
iRow = sh.Cells(Rows.Count, "B").End(xlUp).Row + 1

Robert
 
Upvote 0
Thanks Robert, but that didn't fix it.
I went step by step and fixed it.
VBA Code: 
Sub Reset()

    Dim iRow As Long

iRow = [Counta(Database!A:A)] ' idetifying the last row



With frmForm

.TxtCompanyName.Value = ""
.txtProjectName.Value = ""
.TxtDate.Value = ""
.TxtNo.Value = ""


.CmbConVar.Clear

.CmbConVar.AddItem "Subcontract"
.CmbConVar.AddItem "Variation"

.TxtQuoteNo.Value = ""
.TxtItemNo.Value = ""
.TxtArea.Value = ""
.TxtDes1.Value = ""
.TxtDes2.Value = ""
.TxtHeight.Value = ""
.TxtLength.Value = ""
.TxtLevels.Value = ""
.TxtErect.Value = ""
.TxtHUI1.Value = ""
.TxtHUI2.Value = ""
.TxtHUI3.Value = ""
.TxtSTD.Value = ""
.TxtLCS.Value = ""
.TxtIB.Value = ""
.txtTMNo.Value = ""
.TxtTM.Value = ""
.TxtHUMNo.Value = ""
.TxtHUM.Value = ""
.TxtDismantle.Value = ""
.TxtTransport.Value = ""
.TxtSCS.Value = ""
.TxtSundry.Value = ""
.TxtSupplyBeams.Value = ""
.TxtOthers.Value = ""
.TxtENG.Value = ""
.TxtHire.Value = ""
.TxtHireWeeks.Value = ""
.TxtOH.Value = ""
.TxtWklyHire.Value = ""
.TxtTotal.Value = ""

.lstdatabase.ColumnCount = 34
.lstdatabase.ColumnHeads = True

.lstdatabase.ColumnWidths = "40,60,50,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60"

If iRow > 1 Then

.lstdatabase.RowSource = "Database!A2:AH" & iRow
Else

.lstdatabase.RowSource = "Database!A2:AH2"

End If



    End With



End Sub
Sub Submit()

    Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Database")

iRow = [Counta(Database!A:A)] + 3


With sh



.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = frmForm.TxtDate.Value
.Cells(iRow, 3) = frmForm.TxtNo.Value
.Cells(iRow, 4) = frmForm.CmbConVar.Value
.Cells(iRow, 5) = frmForm.TxtQuoteNo.Value
.Cells(iRow, 6) = frmForm.TxtItemNo.Value
.Cells(iRow, 7) = frmForm.TxtArea.Value
.Cells(iRow, 8) = frmForm.TxtDes1.Value
.Cells(iRow, 9) = frmForm.TxtDes2.Value
.Cells(iRow, 10) = frmForm.TxtHeight.Value
.Cells(iRow, 11) = frmForm.TxtLength.Value
.Cells(iRow, 12) = frmForm.TxtLevels.Value
.Cells(iRow, 13) = frmForm.TxtErect.Value
.Cells(iRow, 14) = frmForm.TxtHUI1.Value
.Cells(iRow, 15) = frmForm.TxtHUI2.Value
.Cells(iRow, 16) = frmForm.TxtHUI3.Value
.Cells(iRow, 17) = frmForm.TxtSTD.Value
.Cells(iRow, 18) = frmForm.TxtLCS.Value
.Cells(iRow, 19) = frmForm.TxtIB.Value
.Cells(iRow, 20) = frmForm.txtTMNo.Value
.Cells(iRow, 21) = frmForm.TxtTM.Value
.Cells(iRow, 22) = frmForm.TxtHUMNo.Value
.Cells(iRow, 23) = frmForm.TxtHUM.Value
.Cells(iRow, 24) = frmForm.TxtDismantle.Value
.Cells(iRow, 25) = frmForm.TxtTransport.Value
.Cells(iRow, 26) = frmForm.TxtSCS.Value
.Cells(iRow, 27) = frmForm.TxtSundry.Value
.Cells(iRow, 28) = frmForm.TxtSupplyBeams.Value
.Cells(iRow, 29) = frmForm.TxtOthers.Value
.Cells(iRow, 30) = frmForm.TxtENG.Value
.Cells(iRow, 31) = frmForm.TxtHire.Value
.Cells(iRow, 32) = frmForm.TxtHireWeeks.Value
.Cells(iRow, 33) = frmForm.TxtOH.Value
.Cells(iRow, 34) = frmForm.TxtWklyHire.Value
.Cells(iRow, 35) = frmForm.TxtTotal.Value
.Cells(iRow, 36) = Application.UserName
.Cells(iRow, 37) = [text(Now(),"DD-MM-YY HH:MM:SS")]


    End With


End Sub


Public Sub Show_Form()
frmForm.Show
End Sub
 
Upvote 0
Thanks Robert, but that didn't fix it.
I went step by step and fixed it.
Click to expand...

Strange, that's where it failed for me. You didn't say what line was failing to it was very hard to troubleshoot.

As long as you've got it working now that's great.
 
Upvote 0
I need help with something else if you can help. in Userform, when I enter numbers, I want them to look as dollar signs. How do I get this to do this? e.g. .Cells(iRow, 13) = frmForm.TxtErect.Value
 
Upvote 0
I don't think you can format a text box but you can format its contents when transferring it to the sheet like so:

VBA Code: 
.Cells(iRow, 13).Value = Format(frmForm.TxtErect.Value, "$#,##0.00;-$#,##0.00")

If every value in Col. M is to be as dollars you can just format the entire column.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Livin404
  • Question
Correcting Run-time error '13' Type Mismatch
Replies
3
Views
401
Livin404
Livin404
Livin404
  • Question
Getting run-time error using User Form
2
Replies
11
Views
618
dmt32
D
K
  • Question
Copying Data entered from a form in one workbook to another?
Replies
0
Views
363
kaia2001
K
A
  • Solved
VBA code for a User form
Replies
4
Views
570
Atlantis764
A
C
  • Question
Run-Time Error '380' When testing the macro in VBA Excel (Rowsource Property issue)
Replies
1
Views
1K
CuriousMind1917
C

Forum statistics

Threads
1,215,518
Messages
6,125,292
Members
449,218
Latest member
Excel Master

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