Multiple user forms in excel

[newuser08]

Hi there

I'm a new user to excel and just coming to terms with it.

Here’s my question:

Is it possible to have Multiple user forms in excel amending data in one xls worksheet?

I have a book that contains a large amount of data that Multiple users (7) need to work on at the same time.
Currently having 1 main data dump (Master.xls) with 7 other xls (User1 to 7.xls) books updating the records via an 'IF Statement' and linked cell references. The master.xls holding customer name (A) tel no(B) contact preference(C) and alternate tel no(D). The Uer books pull the date via a linked cell and the guys then input their findings in their own books. The master then uses linked cells to take the inputted data and show in the appropriate rows.

As you can imagine a bit slow and unstable.

So what I would like to do is make excel work like MSAccess (if this is possible). MSAccess is not available for use to use.

I would like the master to copy if daily data equally into the users books so that they can work the data then when complete an on save function to export all of the data back into the master (We only have excel as a platform).

Any advise, thoughts or help would be appreciated

 

[ck1999]

You could create a database as the backend and use excel as the front end.

I believe you can create and build a database in excel without actually having access.


ck1999

 

[newuser08]

Hi ck1999 Thanks for your reply

Unfortunately we haven’t got the use of Access.
How would I create/build a database in excel?

 

[ck1999]

I have used this before

Public Const DbName As String = "\\server\network\enviroserve\blendsheet\finished.mdb"

Sub initdatabas()   ' To Create the database
    Dim newdb As Database, newws As Workspace
    Dim dbopts As Long
    Set newws = DBEngine.Workspaces(0)
    dbopts = dbversion25 + dbEncrypt
    Set newdb = newws.CreateDatabase(DbName, dbLangGeneral, dbopts)
    Dim newtable As TableDef
    Set newtable = newdb.CreateTableDef("Finished Goods")
    Dim f1 As Field, f2 As Field, f3 As Field
    Set f1 = newtable.CreateField("Product_Name", dbText, 25)
    Set f2 = newtable.CreateField("Lot No #1", dbText, 12)
    Set f3 = newtable.CreateField("Quantity #1", dbInteger, 12)
    f1.Attributes = dbUpdatableField: f2.Attributes = dbUpdatableField: f3.Attributes = dbUpdatableField

    newtable.Fields.Append f1
    newtable.Fields.Append f2
    newtable.Fields.Append f3
    newdb.TableDefs.Append newtable
    newdb.Close
    
End Sub

This was in excel 97. Also I think you have to reference access

ck1999

 

[RivetHed]

Is there any particular reason you don't just share the spreadsheet? They do seem to corrupt a lot more often than unshared workbooks but it would be the quickest and easiest solution.

 

[newuser08]

Thanks for the advise but i'm am unable to ref access.

Any other ideas

 

[newuser08]

Hi RivetHed,

the user xls books have input forms and no visible worksheets the only way the users see the data is by searching with an ref number. So the share function is not an option, also they need to work unique data, and i don't want them having to colour code or "you work 1 to 50, I'll do 51 to 101" scenario

 

[ck1999]

Thanks for the advise but i'm am unable to ref access.

try referencing

microsoft doa object library
and microsoft access object library

in vba editor go to tools/references

ck1999

 

[RivetHed]

You could always use to protection to hide the master worksheets you don't want them seeing and leave them the userform to work with.

Are your users just doing basic data entry? Are their likely to be any conflict issues with 2 of them trying to make changes 2 the same record at the same time? If so what measures do you currently have in place for handling conflicting edits?

 

[newuser08]

Hi RivetHed,

there should be no conflicts, and yes the users are doing basic entry.

Are they all able to have entry to the master.xls and all use the data search and entry forms at the same time?

 

[newuser08]

ck1999

ok i'll try that, as i said im new to excel so am unaware of all the functions etc.

 

[RivetHed]

Not 100% if multi user access to the same user form works in Excel, it does in Access so I'd have thought you'd be ok. Easy answer would be give it a go and see if it works, if it does all your users can work away on the same workbook without any links involved.

 

[newuser08]

Hi RivetHed,

It did work had to remove some macros nothing to major. The conflicts you mentioned haven’t had any yet but what would be the best way to prevent this occurrence?

 

[RivetHed]

Whenever you save a shared workbook Excel will flag up any conflicts to the user, I'd just recommend saving the workbook frequently so any conflicts will result in minimal disruption. There's an option under Share workbook to autosave every x minutes, just set that to something pretty frequent.

 

[newuser08]

cheers RivetHed

 

[kfleeman]

I would never recommend using excel as a database. Download SQL Express if you have no other options available, it is the current MSDE version of SQL Server. Also get the SQL Server Management Studio Express so you can manage the database you create.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

 

[newuser08]

Thanks kfleeman,
Unfortunately our IT have the systems locked down and am unable to download/install anything.
I am used to Orical or SQL and the end user app would have been created in either MSAccess of VB, but again this was a no go area with the response of “you have excel, and that’s all you’ll ever have”.

Cheers anyway.