userform question
you could try testing to see if the workbook is already open by assigning an
object variable to it and testing that variable to see if it is nothing. BTW
- you should declare your variables at the top of your code, not in the
middle - it's generally better practice.
I've not got time to test it, but I hope you get the idea:
Dim wkbTest As Workbook
Dim LastRow As Object '< maybe this could be declared as a range?
on error resume next
set wkbTest = workbooks("Production Interim Rework Log.xls")
on error goto 0
if wkbTest is nothing then
Workbooks.Open Filename:= _
"J:\Personal\ Production Interim Rework Log.xls"
end if
Sheets("BATCH 1").Select
Set LastRow = Range("a65536").End(xlUp)
'rest of your code.....
"DawnRanee" wrote:
I am trying to use the code below to enter data into an excel workbook with a
userform. If I click the "Yes" response,(for multiple entries) it will try to
reopen the workbook again. Does anyone have any ideas on how to fix this?
Thanks in advance.
Workbooks.Open Filename:= _
"J:\Personal\ Production Interim Rework Log.xls"
Sheets("BATCH 1").Select
Dim LastRow As Object
Set LastRow = Range("a65536").End(xlUp)
LastRow.Offset(1, 2).Value = TextBox1.Text
LastRow.Offset(1, 4).Value = TextBox2.Text
LastRow.Offset(1, 7).Value = TextBox3.Text
LastRow.Offset(1, 8).Value = TextBox4.Text
LastRow.Offset(1, 9).Value = ComboBox1.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If response = vbYes Then
ComboBox1.Text = ""
TextBox6.Text = ""
ComboBox1.SetFocus
Else
Unload Me
End If
End Sub
|