Excel and VBA

IDontBelieveIt · Post by **IDontBelieveIt** » Sat Aug 30, 2008 5:23 pm

I am developing an application in MS Excel using VBA macros.

I have one sheet that acts as the user interface (pick, lists, dialogues and buttons).

Others sheets are things like a database and control pages as well as general purpose post analysis data dump pages (for maybe viewing and/or printing results of analysis).

A problem I wish to resolve is that I want to 'freeze' on the user interface sheet whenever I access other sheets. But the problem is that if I say clear one of the other sheets by selecting - which I have to do of course - then that sheet is displayed - of course - but I don't want it displayed.

I recall many moons ago there is a way of not displaying the selected sheet but staying on the current sheet.

Any ideas or a prompt may shake me old grey cells to recalling how it is done.

Cheers

Hoovie · Post by **Hoovie** » Sat Aug 30, 2008 5:53 pm

There are a few ways, depending on what you are doing, what code you have got, etc - basically it is around the "Sheets()" command.

Alternatively if you are just updating info and don't want the worksheets to "bounce around" while there are doing the update, then you can look at the command:

Application.ScreenUpdating

use Application.ScreenUpdating = False before you start the updating and then Application.ScreenUpdating = True afterwards once your script has done the business.

That sounds like it will do what you want (I use that along with the Application.Calculation control a lot to make the workbook look nice and clean when working)

If you send me the workbook, then I can have a look and put some suitable code in and highlight what it is I added.

IDontBelieveIt · Post by **IDontBelieveIt** » Sat Aug 30, 2008 5:56 pm

Ya great Hoovie - that is EXACTLY what I recall (Application.ScreenUpdating = False )

Odd as you get older you forget some of these nuances.

Many thanks Hoovie it is very much appreciated.

Unable to send you any code as it is on my PC (MS Windoze) at work, I am on a Mac here at home. But am 100% certain that Application.ScreenUpdating = False will do the job as I did it many years ago and had forgotten.

Hoovie · Post by **Hoovie** » Sat Aug 30, 2008 5:59 pm

don't have to get older for that to happen

I find if I don't use any command for a while (about a week!), I have to open up the VBA editor on an old workbook to check the syntax

IDontBelieveIt · Post by **IDontBelieveIt** » Sat Aug 30, 2008 6:07 pm

Funny you should say that Hoovie, I was digging around some old applications that I wrote some 10 years ago trying to find out, to no avail!

Usually if I get stuck I do a macro record to prompt the old grey cells but in this case it does not help

Do you have fairly extensive experience using VBA? I rather gather YES is the answer to that based on the quickness of your response.

Hoovie · Post by **Hoovie** » Sat Aug 30, 2008 6:15 pm

well, put it this way ..... after being on this forum for over a year, I finally get to answer a members question based on my own professional knowledge

so THANK YOU, IDBI, for asking

thescruff · Post by **thescruff** » Sat Aug 30, 2008 6:30 pm

I hope you two never get to sit around the same table

Geeeks re-united

IDontBelieveIt · Post by **IDontBelieveIt** » Sun Aug 31, 2008 6:19 am

Found a real gem here Scruff

Post by **ultimatehandyman** » Sun Aug 31, 2008 11:07 am

Hoovie is an expert with excel, he has helped me out a few times

IDontBelieveIt · Post by **IDontBelieveIt** » Sun Aug 31, 2008 1:35 pm

Been using Excel since the mid-80s'.

Excel is an excellent program, has to be it was NOT created by Microsoft - mind they are doing there best to bugger it up completely since buying the original authors out and they ae doing a pretty god job of that

Yup Hoovie knows his stuff OK, problem being that I know that now and will be 'hounding' him..... which reminds me Hoovie....

I have never fathomed out how to run an initialising macro (one that is automatically run upon loading) I have asked several people but to no avail

Hoovie · Post by **Hoovie** » Sun Aug 31, 2008 3:05 pm

First 3 questions are free, then you have to call my 0898 helpline

TBH, I have got lots of great and free advice advice from professionals in THEIR field on this board, so I am only too happy to reciprocate in MINE

IDontBelieveIt wrote: I have never fathomed out how to run an initialising macro (one that is automatically run upon loading) I have asked several people but to no avail

What you do is put the script in the "ThisWorkbook" Object and the routine will then run automatically when the workbook is open.

I use that feature a lot to display a splashscreen on my Excel Apps - here is one at the bottom that Chez will recognise seeing from when he opens the "UHM Monthly Competition" application to put the names in.

To get that to display on startup, the following code is in "ThisWorkbook"

Private Sub Workbook_Open()
On Error Resume Next
ActiveSheet.Protect UserInterfaceOnly:=True
Splash.Show
Sheets("Entrants").Select
End Sub

You can also have a timer in there if you want so the splash close after a few seconds.

Another good use for this autorunning script is to make sure that the right (i.e. the one YOU want ) worksheet is always presented to the user on opening, not the one they last looked at when they saved.

There is a similar process you can do when closing a workbook

One thing people ask and is NOT possible is to by-pass the macro Security Warning prompt.
If you have a workbook that does not have this warning come up but still has macros enabled, then your PC has a security issue and needs sorting ASAP!

IDontBelieveIt · Post by **IDontBelieveIt** » Sun Aug 31, 2008 3:20 pm

Thanks again Hoovie.

Promise not to pester you (too much

) as it happens I will gladly pay for such advice, I am a firm believer in the principle of "I know, you don't you pay". I used to sell software (engineering simulators etc.) so am very familiar with the 'deal'. You clearly know VBA well.

As it happens I have turned off the warning on my PC at work as (a) it is never connected to the web, I never download files that I am not 100% happy with and know the source, and I never load other peoples macros. Many years ago my system was buggered by downloading some Excel macros sent from HQ in the USA. The system I run at work runs the entire companies accounting systems (which I created using Excel and hence I want some finishing touches), there is NO way that is going to be connected to the web.

Is that two or threee questions I have asked?

Yet again Hoovie many thanks - looking foreward to getting the office tomorrow.

IDontBelieveIt · Post by **IDontBelieveIt** » Mon Sep 01, 2008 10:33 am

Ya a grand man Hoovie, it worked just fine!

I know, I know - but of course.

Best forum on the planet this UHM.

IDontBelieveIt · Post by **IDontBelieveIt** » Wed Sep 03, 2008 6:05 pm

Please, PLEASE, can I sneak in another question Hoovie? No OK -

Picklists....

Wanna reset a pick-list item in top of list (ya know linked to a list of items shown within pick-list with a linked cell for selected option).

Never found out how to. Can reset radio buttons in macros etc but not pick-lists

Cheers

PS. Where do I send the cheque?
PPS. Hope ya don't mind me asking here as it may be of interest to others.

Hoovie · Post by **Hoovie** » Wed Sep 03, 2008 6:25 pm

Not sure if I fully understand

, but I'll have a go ....

So you want a cell to give a pick list of choices then, yes? If so, that would be done most easily by this way:
Create your list in a row of cells ... A1=Apples, A2=Oranges,A3=Pears, for example
then set that set to use the list via the Data/Validation option and setting the criteria to List, and have the source be $A$1:$A$3

But then you want to default it to "Apples" in this case? so then the user can change it, but if he doesn't, it stays at apples. And you may also want to reset it back to Apples in case the guy made a screw-up and needs to start again?

There is no specific function I am aware of to do that, but the way I deal with that is to write a macro which will go to each cell I want to reset and set it to the value I want by simply overwriting the existing value.
So if this pick list was in Cell C5, then the macro would be:

Range("C5") = Range("A1")

and just repeat that kind of process as need be for the various cells you want to set at the default.

You can't use a formula in the worksheet as as soon as you select a value from the picklist, it gets overwritten.

Is that the answer to the question you asked? (It may be an answer to a question you didn't ask and didn't have any interest in asking

)