Use UserForm to Access Spreadsheet on Corporate Intranet?

[ryguy7272]

In the office this past week I created a tool that was posted on the
corporate Intranet and users downloaded it to view certain reports and make
forecasts to other reports. The file was 6.8MB and Tools > Share Workbook >
Allow changes… was checked. Over the course of the first day that the tool
was posted to the Intranet, we noticed some strange behavior. Some people
who open the file received a message saying that the file was ‘Read Only’ but
this setting was never enabled. Also, some people were unable to save the
changes that they made to their forecasts (even when the file was not opened
as ‘Read Only’…which was never enabled anyway). Some people had no problems,
whatsoever, accessing and utilizing the file. All in all, probably 30+
people accessed the file throughout the day. Due to this strange, and
seemingly random behavior, I can only imagine that we exceeded some kind of
memory limitation. Has anyone encountered this type of behavior before? If
so, what did you do to overcome these issues?

I am thinking that there must be a better way to allow users to log into a
single file, view the contents, and make a few changes. Can this be done
with UserForms? Can I set up some UserForms, open them, allow users to input
information into the form, and then send this data to a specific range on a
specific sheet? I have some extensively experience programming Excel
UserForms, but I have never tried to use a UserForm to log into a ‘remote’
excel file.

I have some experience using specific references in UserForms such as this
…
Private Sub ComboBox2_Change()
UserForm1.ComboBox2.RowSource =
ThisWorkbook.Worksheets("Welcome").Range("A12:A20").Address(External:=True)
End Sub

Private Sub ComboBox3_Change()
UserForm1.ComboBox3.RowSource =
ThisWorkbook.Worksheets("Welcome").Range("B12:B20").Address(External:=True)
End Sub
…

Anyway, I’m not even sure that this type of project is feasible or
practical. Does anyone have any experience doing such a thing? What may be
a viable alternative? Some people have MS Access, but most people do not,
and will not have it installed, so I am trying to do this project in Excel.


I’d appreciate any and all input.

Regards,
Ryan---

 

[john]

I am only guessing but sounds like you are being prompted to open the file
“Read only†when another user already has it open? Whatever, if you are
sharing a file to read / write data with multiple users I would consider
having the user’s workbooks read / write data to a central database – You
could use Access which is normally available with MS Office or even use
another workbook as the database although this will have limitations. If you
want to go this route there are plenty of experienced people in this forum
that should be able to give your further guidance if needed.

Another alternative would be to consider a professional tool such as TM1.
TM1 is a multidimensional online analytical processing (OLAP) database that
extracts data from ERP systems such as SAP, and other relevant applications
and is a building block for data warehousing activities, TM1 cross-correlates
all the data into a massive "cube" that allows users to analyse trends using
compound queries, e.g. revenues by type/region/profit margin etc. I am not
100% sure, but I think Congos (an IBM company) now own this product.

Hope of some help

 

[ryguy7272]

Thanks for the information john!! I have used Cognos before; it is amazing!
A team of developers is working on a long-term solution for us. In the
meantime, we were trying to come up with a viable short-term solution using
Excel, and maybe Access too. I have Access, and I have done some
intermediate to advanced work in Access before, but many users don't have
Access, so we were trying to stay away from that for the interface part.

Can an Excel UserFrom be shown, without downloading the entire Excel file
from the server, for the purpose of cutting down on memory and bandwidth
usage? Basically, can a UserFrom popup and ask for inputs for the relevant
information? I would like the user to then click a command button to capture
all data in several TextBoxes, for instance, and transmit this data to a
specific sheet in the Excel file which is stored on the server?

I'd really appreciate any other ideas?

Regards,
Ryan---

 

[john]

You can achieve a great deal with Excel especially when connecting to other
platforms to support your application. To give an idea, I developed for my
daughter’s work place a timesheet application as an Excel Addin. 250 users
submit timesheets each week and although my first preference was to use
Access as the database, they too only wanted to use Excel. The Addin is
installed in each users machine & this has number of userforms for data
entry. Depending on activity, users can read / write to database. Apart from
need to manage the physical size of the data stored in the database sheet, so
far (going for about two years now) they have not encounter any major
problems.

On the downside, this was a major development which would have been better
served using a database like Access but Excel can be pushed to limits to do
some quite surprising tasks.
In the end, it really depends how much effort you want to put in developing
your application. Personally for a Budget / Forecast application of
significant size, I would look at something like TM1, it will do all you want
and much more!

Hope useful.

 

[ryguy7272]

Excellent!! Indeed, excel is a powerful too. Do you have a mini version of
that tool? Can you send me one Userform and one Sheet to capture and store
the data? I think I can figure out the rest. I’m stumped as to how to get
the UserForm away from the Sheet. In other words, I don’t know how to do
this without giving users access to the whole Workbook. If you could send me
one Form and one Sheet, with a blurb or two if there is some really technical
stuff, I’d really, really, really appreciate it.

(e-mail address removed)
(without the xx part)


Regards,
Ryan---

 

[john]

Sorry but don’t have anything in short form at moment that I can send
directly as a working example, the application I mention is property of my
daughters company & unable to distribute any part of it – There are some
very good sites that have good working examples of copying data to other
workbooks and creation of userforms etc:

http://www.rondebruin.nl/tips.htm

http://www.contextures.com/excelfiles.html

Suggest take a look at these (and any others you can find via say google) &
should point you in right direction. As I said earlier, you can post any code
problems to this forum & most here will be happy to give further guidance if
need. If I do get a moment, i will see if I can cobble something together.

Hope helpful

 

[ryguy7272]

Yes, those two links are very helpful. I have used both resources, multiple
times, in the past. I just seem to have a mental block about how to do this
thing. I'm going to sleep on it; hopefully something will come to mind.

Thanks again for the information john!!

Regards,
Ryan---