displaying message when opening Excelfile 2

[blom0344]

The search module is disabled at the moment, so a bit of a trivial thing:
I want to display a popup message to give users some idea what they can find in the Excel-file they are receiving. It should be the first thing they see prior to viewing the contents of the Excel-file.
Any idea , perhaps in a simple VBA script? T. Blom
Information analyst
tbl@shimano-eu.com

 

[mudstuffin]

Is this what you're after.....


Private Sub Workbook_Open()

MsgBox "Message contents here", vbOKOnly, "The title goes here"

End Sub


or if you want to go on to new lines:-


Private Sub Workbook_Open()

MsgBox "Message contents here" & Chr(10) & Chr(10) & "next line", vbOKOnly, "title goes here"

End Sub


Regards,


mudstuffin

 

[blom0344]

Yeah,

I knew I was asking something trivial, but It did not come to mind. Thanx for the example.......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Sorry to extend my question - Excel key-user still recovering from illness and I am strictly a database guy -, but with large amounts of text to be displayed, how can I open a custom-made form instead of a messagebox?
I know this is a very basic thing, but I am just covering someone elses job.

Every suggestion appreciated......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[mbarron]

blom0344,

I don't have an answer, only a suggestion. You may have already done this, but if you haven't - repost your last post as a new thread.

Mudstuffin (and others who may be able to help on the launching of the form) probably sees the star (and maybe saw your "Thanx" response) on this post and assume that the problem is solved.

I'm here because I saw the subject and the star and knew my answer was probably in you post. Mike
If you're not part of the solution, you're part of the precipitate.

 

[mudstuffin]

Blom0344,

No problem at all. Here's what you need to do...

Firstly, open up the VBE and go to the Insert menu, and insert a Userform. This should be called UserForm1.

Then add your any TextBoxes, Labels etc to the form, and then type your text in to the text box. Make sure you change the Multiline property of the text box to 'True'.

In the workbook_open event, add:

UserForm1.Show

to open it

To close it, maybe add a CommandButton to the form and add:

UserForm1.Hide

to its click event.

If the text you wanted to use was stored in the spreadsheets cells, or maybe got updated automatically etc, you could always pull that to the textbox on the userform.

i.e.

Private Sub UserForm_Activate()

TextBox1.Text = Worksheets("Sheet1&quot.Range("a1&quot.Text

End Sub


the above is assuming your text is in a1 of Sheet1...!

Anything else, just reply back to this thread.

n.b. using simple userforms are quite handy for using as a password login, as you can apply a password character to mask the input.

Regards,


Mudstuffin.

p.s. I keep an eye on the threads that I've been involved in as it appears in 'My Threads' so no need to start a new thread. It's better like this as you and other users can see how problems get solved form start to finish, and how they can lead on, and essentially, this is still the same question, just a different way of going about it.

 

[blom0344]

Hello Mudstuffin,

Thanks for the excellent coverage......
Can I draw upon your knowledge a little further?

Imagine the sheet containing information about sales. The sheet is distributed to 10 sales-reps, but management wants to split information so that a salesrep can only see information of their own salesarea (as defined by a salesrep number) At the moment all information is downloaded into pivottables from a central source using MS Query. Adjusting the queries to put conditions on the salesreps is a lot of work, so I am looking for a way to restrict information to a certain salesrep by means of validating the login to the sheet into setting a hard filter on the information.

Can this be done and if yes, do you have suggestions? T. Blom
Information analyst
tbl@shimano-eu.com

 

[mudstuffin]

blom0344,

I reckon it can be done.

I've knocked up a workbook, and have emailed it to you.

Basically, when you open the workbook up, a log-in form is opened. The user then picks their username from the combobox, and types in the password.

The data sheet then opens, and filters ONLY ON that users data. I have also disabled the filter option from the menu, in an attempt to stop the user simply taking the filter off. (It will re-enable when the workbook closes, is deactivated, or when logged in as user 'password'.)

When a user has finished, they click a button to log out, which hides the data sheet again and takes them to the startup sheet. There is also a login for admin, which shows all the sales data, and another one to be able to access the users and the passwords table.

Oh yeah, and if the passwords are entered incorrectly 3 times, the workbook closes.

Let me know if there's any problems, or if you have any questions.

Hope it helps.

regards,


mudstuffin.