Excel and VBA

FAQ Search Register (it's completely free)

Profile Log in to check your private messages Log in

Excel and VBA
Goto page 1, 2 Next

www.ultimatehandyman.co.uk Forum Index -> Computers

View previous topic :: View next topic

Author

Message

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sat Aug 30, 2008 5:23 pm Post subject: Excel and VBA

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
_________________
Never lie - that way you don't have to remember what you said.

Hoovie
Devon DIYer

Joined: 27 Jul 2007
Posts: 8040
Location: East Devon

Posted: Sat Aug 30, 2008 5:53 pm Post subject:

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.
_________________
I went to a bookstore and asked the saleswoman, "Where's the self-help section?"
She said if she told me, it would defeat the purpose.

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sat Aug 30, 2008 5:56 pm Post subject:

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.
_________________
Never lie - that way you don't have to remember what you said.

Hoovie
Devon DIYer

Joined: 27 Jul 2007
Posts: 8040
Location: East Devon

Posted: Sat Aug 30, 2008 5:59 pm Post subject:

don't have to get older for that to happen Laughing

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
_________________
I went to a bookstore and asked the saleswoman, "Where's the self-help section?"
She said if she told me, it would defeat the purpose.

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sat Aug 30, 2008 6:07 pm Post subject:

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 Laughing

Do you have fairly extensive experience using VBA? I rather gather YES is the answer to that based on the quickness of your response.
_________________
Never lie - that way you don't have to remember what you said.

Hoovie
Devon DIYer

Joined: 27 Jul 2007
Posts: 8040
Location: East Devon

Posted: Sat Aug 30, 2008 6:15 pm Post subject:

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 Thumbright

so THANK YOU, IDBI, for asking Laughing

_________________
I went to a bookstore and asked the saleswoman, "Where's the self-help section?"
She said if she told me, it would defeat the purpose.

thescruff
Senior Member

Joined: 10 Mar 2008
Posts: 5713
Location: Bath

Posted: Sat Aug 30, 2008 6:30 pm Post subject:

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

Geeeks re-united Thumbright

_________________
Scruff

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sun Aug 31, 2008 6:19 am Post subject:

Found a real gem here Scruff Thumbright

_________________
Never lie - that way you don't have to remember what you said.

ultimatehandyman
Site Admin

Joined: 16 Jul 2005
Posts: 9756
Location: Darwen, Lancashire

Posted: Sun Aug 31, 2008 11:07 am Post subject:

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

_________________
DIY | DIY Video

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sun Aug 31, 2008 1:35 pm Post subject:

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 Wink

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 scratch

_________________
Never lie - that way you don't have to remember what you said.

Hoovie
Devon DIYer

Joined: 27 Jul 2007
Posts: 8040
Location: East Devon

Posted: Sun Aug 31, 2008 3:05 pm Post subject:

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

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 Thumbright

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 scratch

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 Thumbright

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!

UHMComp.jpg

Description:

Filesize:

158.48 KB

Viewed:

246 Time(s)

_________________
I went to a bookstore and asked the saleswoman, "Where's the self-help section?"
She said if she told me, it would defeat the purpose.

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Sun Aug 31, 2008 3:20 pm Post subject:

Thanks again Hoovie.

Promise not to pester you (too much Wink

) 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? scratch

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

_________________
Never lie - that way you don't have to remember what you said.

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Mon Sep 01, 2008 10:33 am Post subject:

Ya a grand man Hoovie, it worked just fine!

I know, I know - but of course.

Best forum on the planet this UHM.
_________________
Never lie - that way you don't have to remember what you said.

IDontBelieveIt
Approved Electrician

Joined: 29 Jul 2008
Posts: 1210
Location: North Hampshire

Posted: Wed Sep 03, 2008 6:05 pm Post subject:

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 scratch

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.
_________________
Never lie - that way you don't have to remember what you said.

Hoovie
Devon DIYer

Joined: 27 Jul 2007
Posts: 8040
Location: East Devon

Posted: Wed Sep 03, 2008 6:25 pm Post subject:

Not sure if I fully understand scratch

, 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 Laughing

)
_________________
I went to a bookstore and asked the saleswoman, "Where's the self-help section?"
She said if she told me, it would defeat the purpose.

Display posts from previous:

	www.ultimatehandyman.co.uk Forum Index -> Computers	All times are GMT Goto page 1, 2 Next
Page 1 of 2

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum

ultimatehandyman privacy policy

Diy forum - Decking - plastering - Plumbing - DIY - Tiling