How do you program so that Workbook B cannot be open unless Workbook A is open? Plus I need to valid

[Marcello do Guzman]

so that Workbook B would check for a certain value in a worksheet in
Workbook A. If you have the VBA code for this please let me know.
Thanks in advance.


TooShyNiceGuy

You can post answer here or send me email at:

(e-mail address removed)

 

[K Dales]

If Workbook B and Workbook A are both already open in the same Excel session
it is easy - From your VBA code in Workbook B, it would look like this:
ValueFromA = Workbooks("Workbook
A").Sheets(SheetName).Range(CellAddress).Value

If Workbook B is NOT already open you need to open it first:
Workbooks.Open (Filename As String, [UpdateLinks], [ReadOnly], [Format],
[Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin],
[Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local],
[CorruptLoad]) - see Excel help "Programming Information" for a complete
explanation of all the parameters.

 

[Sharad Naik]

Assume the names of workbook are "A.xls" and "B.xls".
Assume for validation, you want to check value in cell F10 in Sheet1 of
A.xls.

Then in "B.xls" , add following code in Thisworrkbook -> Workbook_Open
procedure:

Private Sub Workbook_Open()
On Error Resume Next
If Workbooks("A.xls").Parent Is Nothing Then
MsgBox "Workbook 'A.xls', is not open. Therefore this" & _
"workbook will close"
ThisWorkbook.Close SaveChanges:=False
Exit Sub
End If

If Workbooks("A.xls").Sheet1.Range("F10").Value = _
"whatever value you want to check" Then
'Your further code if the value is correct
Else
'your code if the value is not correct
End If

End Sub

Another alternative could be instead of closing workbook B.xls, if workbook
A.xls is not open
you can make the code to open 'A.xls'. If you wish to do this the code will
be as under, assuming
A.xls is in C:\My Documents

Private Sub Workbook_Open()
On Error Resume Next
If Workbooks("A.xls").Parent Is Nothing Then
Workbooks.Open Filename:="C:\My Documents\A.xls"
End If
If Workbooks("A.xls").Parent Is Nothing Then Exit Sub
If Workbooks("A.xls").Sheet1.Range("F10").Value = _
"whatever value you want to check" Then
'Your further code if the value is correct
Else
'your code if the value is not correct
End If

End Sub