userform question

[Guest]

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

 

[Guest]

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.....

 

[tissot.emmanuel]

Hi,

You probably place your code in the Combobox1_Change or TextBox6_Change sub,
so when you change the value of ComboBox1 or TextBox6 the event rise again
and the Workbooks.Open statement is executed once again. To Avoid it, add a
test to determine if the workbook has been already opened.

Dim Wbk As Workbook
On Error Resume Next
Set Wbk = Workbooks("Production Interim Rework Log")
If Wbk Is Nothing Then
Workbooks.Open Filename:= "J:\Personal\ Production Interim Rework
Log.xls"
End If
On Error GoTo 0

Regards,

Manu/

 

[Guest]

Thanks for your help. This works for me!

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.....