Dear Advisers
I have a workbook with 2 sheets. One of the sheets is for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I can
input details in a Form which are relevant to the details
of the 7 Column Heads of the Vendor Sheet. Assuming that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic

Dominic,

If I understand correctly, you use VLOOKUP to get the details

=VLOOKUP(Sheet1!A1,Sheet2!A1:H100,7,FALSE)

and yoou would use that value to populate your textbox say on the form.

In my example, the vendor in A1 is used to look up the corresponfing value
in column G. You don't say how the selected vendor would be made known to
your form.

--
HTH

Bob Phillips

"Dominic" wrote in message
...
Dear Advisers
I have a workbook with 2 sheets. One of the sheets is for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I can
input details in a Form which are relevant to the details
of the 7 Column Heads of the Vendor Sheet. Assuming that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic

Dear Bob
Thanks for your reply! and Yes I do use the VLOOKUP
function to derive the details.
I think my needs were not clearly stated in my initial
request for help! So let me rephrase myself and let you
understand what I am looking for.
I have 2 worksheets in my workbook, which are PO Sheet(PO)
and Vendor Sheet(VS). VS has the following details: Name,
Company, Phone, Fax, POBOx, Code, Country which are all
Column headers.
I want these headers to be used in the excel Form to input
data. Now if I am in the VS sheet I can do it simply by
going to the Data Menu\ Form and I can enter data easily.
Now what I want to do is to hide the VS sheet and have
only the PO sheet visible and if I call on a macro by
pressing any button (such as F1-F12) I should get the same
Form of the VS Sheet (Name, Company, Phone, Fax, etc...)
whereby I can enter the details in this form and they are
entered in the VS Sheet automatically, eventhough the VS
Sheet is hidden.
I hope I made myself clear and apologies for my previous
mistake in not being precise.
Thanks
Dominic

-----Original Message-----
Dominic,

If I understand correctly, you use VLOOKUP to get the
details

=VLOOKUP(Sheet1!A1,Sheet2!A1:H100,7,FALSE)

and yoou would use that value to populate your textbox
say on the form.

In my example, the vendor in A1 is used to look up the
corresponfing value
in column G. You don't say how the selected vendor would
be made known to
your form.

--
HTH

Bob Phillips

"Dominic" wrote in
message
...
Dear Advisers
I have a workbook with 2 sheets. One of the sheets is
for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I
can
input details in a Form which are relevant to the
details
of the 7 Column Heads of the Vendor Sheet. Assuming
that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic


.

Dominic,

You are right, you weren't clear, but you are now :-).

Here is a simple macro to do it. I was surprised that this works with a
hidden sheet, but it seems to.

Sub ShowVS()
Worksheets("VS").ShowDataForm
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominic" wrote in message
...
Dear Bob
Thanks for your reply! and Yes I do use the VLOOKUP
function to derive the details.
I think my needs were not clearly stated in my initial
request for help! So let me rephrase myself and let you
understand what I am looking for.
I have 2 worksheets in my workbook, which are PO Sheet(PO)
and Vendor Sheet(VS). VS has the following details: Name,
Company, Phone, Fax, POBOx, Code, Country which are all
Column headers.
I want these headers to be used in the excel Form to input
data. Now if I am in the VS sheet I can do it simply by
going to the Data Menu\ Form and I can enter data easily.
Now what I want to do is to hide the VS sheet and have
only the PO sheet visible and if I call on a macro by
pressing any button (such as F1-F12) I should get the same
Form of the VS Sheet (Name, Company, Phone, Fax, etc...)
whereby I can enter the details in this form and they are
entered in the VS Sheet automatically, eventhough the VS
Sheet is hidden.
I hope I made myself clear and apologies for my previous
mistake in not being precise.
Thanks
Dominic

-----Original Message-----
Dominic,

If I understand correctly, you use VLOOKUP to get the
details

=VLOOKUP(Sheet1!A1,Sheet2!A1:H100,7,FALSE)

and yoou would use that value to populate your textbox
say on the form.

In my example, the vendor in A1 is used to look up the
corresponfing value
in column G. You don't say how the selected vendor would
be made known to
your form.

--
HTH

Bob Phillips

"Dominic" wrote in
message
...
Dear Advisers
I have a workbook with 2 sheets. One of the sheets is
for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I
can
input details in a Form which are relevant to the
details
of the 7 Column Heads of the Vendor Sheet. Assuming
that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic


.

This sounds like a classic use of Excel's DataForm. Keep the database of
information on a hidden sheet and call the Dataform from another sheet, to
modify the database. What I have done is similar. I have several databases
on 1 sheet, so my code is geared for that.
With the Vendor data on sheet2 and that sheet tab named "Data" and the upper
left cell of your data at A1, scroll to the right using the horizontal
scroll bar, untill your data is off the screen (this is important to keep
your data from being visible behind the DataForm). Now hide that sheet. Put
the below code in a standard code module. On a visible sheet (Sheet1) create
a CommandButton, label it something like "Update Vendors" and assign this
macro to it.

Sub Vendors()
ws = ActiveSheet.Name 'Stores the current sheet you are on
Sheet2.Activate 'adjust to the sheet your database is on
Range("A1:G3").Select Adjust to select your first header & first row of
data
ActiveCell.CurrentRegion.Select 'selects to last row of data
Selection.Name = "Database" 'Do not change
ActiveSheet.Name = "Vendors" 'Do not change
ActiveSheet.ShowDataForm 'Do not change, code stops here until DataForm
is closed
ActiveSheet.Name = "DATA" 'Change to match name on sheet tab where data
is
Range("A1").Select 'resets cursor on database sheet for next time
Sheets(ws).Activate 'Selects the sheet you started from
End Sub

Cheers,
Mike F

"Dominic" wrote in message
...
Dear Advisers
I have a workbook with 2 sheets. One of the sheets is for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I can
input details in a Form which are relevant to the details
of the 7 Column Heads of the Vendor Sheet. Assuming that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic

Correction. I forgot the Rem mark (') on line 3 before the word "Adjust".
Some Clarification: I do not use a button to fire my dataform. I have
several databases on the hidden sheet. I have many visible sheets.
My list of databases in on the menu bar so the dataform can be brought up
from any visible sheet in the workbook. That is why I store the current
Activesheet in a variable, so the macro will take you back to the sheet you
were on.
Second, line 6 (ActiveSheet.Name = "Vendors" 'Do not change). The sheet
name would change if you had a second database on the hidden sheet,
separated from the first by an empty column or row. Suppose Columns I1:M1 is
a database of "Vendor Purchases". Rename the same macro to Sub Vendor
Purchases(), adjust the range on line 3 for this dataset, and change line 6
to ActiveSheet.Name = "Vendor Purchases".
What this does is declare that specific range of data on the hidden sheet as
a database with a specific name (line 6) so the DataForm can show only that
part of the hidden sheet. It actually renames the hidden sheet using only
the selected range of data as a database sheet. When you close the DataForm,
line 8 puts the hidden sheet name back to what it was.

I hope this clears up some of what happens with the code.
Mike F

"Mike Fogleman" wrote in message
...
This sounds like a classic use of Excel's DataForm. Keep the database of
information on a hidden sheet and call the Dataform from another sheet, to
modify the database. What I have done is similar. I have several databases
on 1 sheet, so my code is geared for that.
With the Vendor data on sheet2 and that sheet tab named "Data" and the
upper
left cell of your data at A1, scroll to the right using the horizontal
scroll bar, untill your data is off the screen (this is important to keep
your data from being visible behind the DataForm). Now hide that sheet.
Put
the below code in a standard code module. On a visible sheet (Sheet1)
create
a CommandButton, label it something like "Update Vendors" and assign this
macro to it.

Sub Vendors()
ws = ActiveSheet.Name 'Stores the current sheet you are on
Sheet2.Activate 'adjust to the sheet your database is on
Range("A1:G3").Select Adjust to select your first header & first row of
data
ActiveCell.CurrentRegion.Select 'selects to last row of data
Selection.Name = "Database" 'Do not change
ActiveSheet.Name = "Vendors" 'Do not change
ActiveSheet.ShowDataForm 'Do not change, code stops here until DataForm
is closed
ActiveSheet.Name = "DATA" 'Change to match name on sheet tab where data
is
Range("A1").Select 'resets cursor on database sheet for next time
Sheets(ws).Activate 'Selects the sheet you started from
End Sub

Cheers,
Mike F

"Dominic" wrote in message
...
Dear Advisers
I have a workbook with 2 sheets. One of the sheets is for
Vendors which has 7 Columns Heads for relevant vendor
details. The Other Sheet is bascially a lookup of the
Vendor Details and Additional Data.
I would like to do the following:
Create a Macro on the Other sheet that when it Runs, I can
input details in a Form which are relevant to the details
of the 7 Column Heads of the Vendor Sheet. Assuming that
the Vendor Sheet is Hidden.
Thank You in Advance for any assistance rendered.
regards
Dominic