Hello Everyone!
I have developed a macro for a userform that I feel is close to what I want to achieve but I haven't figured out a few issues just yet:
I will have 30 sheets this code needs to run on (named imaginatively 1-30), and I'd prefer the macro to grab the current sheet's reference (the sheet the button was pressed on), and use that to execute the rest of the code.
Where I'm having problems:
-Macro determining name of sheet and using that as the sheet the referred to range is on
-My "estimate" value keeps telling me "Type Mistmatch"
-Getting the data to paste in the sheet
As it stands I know the if/then statements telling users they must enter the required fields works.
Its when I added the code to try to make each new entry paste to the next available line that the macro didn't work (I didn't get an error on this, it just didn't paste the data entered).
Sub Commandbutton1_click()
Dim rowcount As Long
Dim numcheck As Boolean
Dim ebookloc
Dim estimate
numcheck = IsNumeric(TextBox2.Value)
estimate = TextBox2.Value / 16.69
ebookloc = TextBox2.Value
Application.EnableCancelKey = xlDisabled
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
If TextBox1.Value = "" And TextBox2.Value = "" And TextBox3.Value = "" Then
MsgBox "Please fill in Required Fields: Book Title, Author Name, Pages / Locations"
Else
If TextBox1.Value = "" Then
MsgBox "Book Title Required!"
Else
If TextBox2.Value = "" Then
MsgBox "Total Page (or) Location number Required!"
Else
If TextBox3.Value = "" Then
MsgBox "Author Name Required!"
Else
If numcheck = False Then
MsgBox "Page or Location field may only contain a numerical value!"
If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" And ComboBox1.Value = "ebook" Then
'ActiveSheet.Unprotect Password:="1"
'This code is to paste each new entry on the next row. I have seen it work in another workbook,but not when combined with the If/Then contingent.
rowcount = Worksheets("1").Range("e12").CurrentRegion.Rows.Count
With Worksheets("1").Range("e12")
.Offset(rowcount, 1) = ComboBox1.Value
.Offset(rowcount, 2) = "'" & TextBox1.Value
.Offset(rowcount, 3) = estimate
.Offset(rowcount, 4) = ebookloc
.Offset(rowcount, 5) = "'" & TextBox3.Value
.Offset(rowcount, 6) = ComboBox2.Value
.Offset(rowcount, 7) = "'" & TextBox4.Value
.Offset(rowcount, 8) = Now("mm/dd/yyyy")
End With
'ActiveSheet.Protect Password:="1"
Else
If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" And ComboBox1.Value <> "ebook" Then
'ActiveSheet.Unprotect Password:="1"
'This code is to paste each new entry on the next row. I have seen it work in another workbook,but not when combined with the If/Then contingent.
rowcount = Worksheets("1").Range("e12").CurrentRegion.Rows.Count
With Worksheets("1").Range("e12")
.Offset(rowcount, 1) = ComboBox1.Value
.Offset(rowcount, 2) = "'" & TextBox1.Value
.Offset(rowcount, 3) = TextBox2.Value
.Offset(rowcount, 4) = "N/a"
.Offset(rowcount, 5) = "'" & TextBox3.Value
.Offset(rowcount, 6) = ComboBox2.Value
.Offset(rowcount, 7) = "'" & TextBox4.Value
.Offset(rowcount, 8) = Now("mm/dd/yyyy")
End With
'ActiveSheet.Protect Password:="1"
End If
End If
End If
End If
End If
End If
End If
Unload NewEntry
End Sub
Thank you all in advance!!
Bookmarks