Macro User form

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
I have a user form to enter data in certain columns on sheet "Fassets"
I get subscript out of range and the code below is highlighted

Code:
 Set ws = Worksheets("Fassets")


It would be appreciated if someone could kindly amend my code


Code:
 Option Explicit

Private Sub CMDAdd_Click()
Dim I As Integer
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Fassets")



'check for a Asset Type
If Len(Me.TxtAssetype.Value) = 0 Then
Me.TxtAssetype.SetFocus
MsgBox "Please select the Asset Type from the drop down menu"
Exit Sub
End If

If Len(Me.TxtDes.Value) = 0 Then
Me.TxtDes.SetFocus
MsgBox "Please Enter Description"
Exit Sub
End If

If InStr(Me.Txtcost, ".") = 0 Then
MsgBox "Please Enter Decimal place", vbCritical
Me.Txtcost.SetFocus
Exit Sub
End If

Me.Txtcost = Format(Me.Txtcost, "#,##0.00")

'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
'find row to use
For I = 1 To TextBox1.Value
iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.TxtAssetype.Value
.Cells(iRow, 4).Value = Me.TxtDes.Value
.Cells(iRow, 5).Value = Me.TxtDept.Value
.Cells(iRow, 9).Value = Format(TxtDate1.Value, "mm/dd/yyyy")
.Cells(iRow, 12).Value = Me.Txtcost.Value

Next I
' .Protect Password:="password"
End With

'clear the data
With Me
.TxtAssetype.Value = ""
.TxtDes.Value = ""
.TxtDept.Value = ""
.TxtDate1.Value = ""
.Txtcost.Value = ""

    End With

Sheets("Fassets").Select
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LR).FormulaR1C1 = "=VLOOKUP(RC[-1],Table!R2C1:R22C3,2,FALSE)"
Range("K2:K" & LR).FormulaR1C1 = "=RC[1]"
Range("M2:M" & LR).FormulaR1C1 = "0"
Range("P2:P" & LR).FormulaR1C1 = "=Codes!RC[4]&""=100"""
Range("J2:J" & LR).FormulaR1C1 = "=VLOOKUP(RC[-9],Table!R2C1:R23C3,3,FALSE)"

If Range("A3").Value = Empty Then
Exit Sub
Else
   


Range("R2").copy Destination:=Range("R3:R" & LR)
End If
End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,911
Does worksheet "Fassets" belong to the same workbook that contains the userform?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply.

"Fassets" does belong to the same workbook that contains the userform
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,911
I'm sorry, I asked for the wrong question, that is:
Does worksheet "Fassets" belong to the same workbook that contains the macro?

If Yes, try using
Set ws = ThisWorkbook.Worksheets("Fassets")

If No, then try Set ws = ActiveWorkbook.Worksheets("Fassets")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Also check that the sheet name does not have any leading/trailing spaces.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
thanks Anthony- the worksheet "Fassets" does belong to the same workbook that contains the macro

I have used your code and it works perfectly
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
thanks for your input Fluff

I checked that before you replied as I thought it may be the problem, but it had no trailing or leading spaces
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,960
Members
410,586
Latest member
acadavid86
Top