Excel VBA - Inputbox Problems

  • Thread starter Thread starter ajlove20
  • Start date Start date
A

ajlove20

Hi,

I have a workbook that copies values from a sheet located in mulltiple
workbooks. In the multiple workbooks I have a code that runs when I
try to exit the workbook. This code goes through the sheets in the
workbook and asks the user to fill in locations that were left blank
using an inputbox.

That code works fine, but when I try to copy the values from the sheet,
I get the inputboxes from the other workbooks. I shouldn't get any
inputboxes b/c all of the information in the other workbooks is filled
out. After numerous times hitting CANCEL, it works fine.

I was wondering if there was something I could do to not have the
inputbox come up when copying the value. Any help is appreciated.

Thanks in advance.

aj
 
Here is a snippet of the code that is in the multiple workbooks:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
...

Sheets("TEST & WORK").Select

z = Range("A7:A26").Value
For x = 1 To 20
Next x

Select Case z(1, 1)
Case Is = ""
Case Else
v = Range("A7:E7").Value
For i = 0 To 5
'MsgBox v(1, i)

Select Case Range("B7")
Case Is = ""
Range("B7").Select
Range("B7") = InputBox("Enter whether the 1st test i
VASCIC CANDIDATE (Y/N)", "Missing Info", "", 100, 1)
End Select
...

Select Case Range("BQ18")
Case Is = ""
Range("BQ18").Select
Range("BQ18") = InputBox("Enter the WORK PACKAGE fo
the test...if there is no WORK PACKAGE then Enter N/R", "Missing Info"
"", 100, 1)
End Select
Next i
End Select

Call TryNow1

End Sub



And here is the code I used to copy the value from the sheets:

Sub Fill()
...

SaveDriveDir = CurDir
MyPath = "G:\CVN\110N (cvn70 ROCH)\Databases\Scoping\test"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 3

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
lrow = LastRow(mybook.Sheets("REVIEW"))
Set sourceRange = mybook.Worksheets("REVIEW").Range("A2:IV"
lrow)
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

sourceRange.Copy destrange

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum
"A"). _
Resize(.Rows.Count
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Thank you. I really appreciate you taking time to help me.

a
 
Hi
problem is you just use Range withou qualifying which workbook it
should go to. Also try to remove select statements (use object
references). Try the following (not tested). Also see my comments


Private Sub Workbook_BeforeClose(Cancel As Boolean)
...
Dim wks as worksheet

set wks = Me.Sheets("TEST & WORK")

z = wks.Range("A7:A26").Value
For x = 1 To 20
Next x

Select Case z(1, 1)
Case Is = ""
Case Else
v = wks.Range("A7:E7").Value
For i = 0 To 5
'MsgBox v(1, i)

with wks.range("b7")
Select Case .value
Case Is = ""
..value = InputBox("Enter whether the 1st test is
VASCIC CANDIDATE (Y/N)", "Missing Info", "", 100, 1)
End Select
end with
...

with wks.range("bQ18")
Select Case .value
Case Is = ""
..value= InputBox("Enter the WORK PACKAGE for
the test...if there is no WORK PACKAGE then Enter N/R", "Missing Info",
"", 100, 1)
End Select
end with
Next i
End Select

Call TryNow1

End Sub
 
Hi,

Thank you for your help. It worked fine. Now I have created
userform and I am having the same problem with it popping up durin
copying, just like the I had with the inputboxes. Is there something
need to do with the userform to prevent it from popping up while I a
copying. Thanks in advance.

a
 
Back
Top