vba code to disable only close button and close through a button in Excel workbook

Hi,

Try:

Please Login or Register  to view this content.

In your original code, the 'ThisWorkbook.Close' statement closes the current Excel file, and STOPS ALL MACRO ACTIVITY for ALL FILES in the current instance of Excel.

Lewis

Dear LJMetzger,

Thanks your change is working really better but i want one message box when I click excel close button that use "Sheet Close Button"


I have tried following code in thisworkbook section but the msgbox is appearing with sheet close macro too.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If oktoclose = False Then
'MsgBox "Use the button in sheet to close", , "CB Vault Admin"

'End If

Cancel = Not Ok2Close




End Sub

Spelling error. 'oktoclose' should be 'Ok2Close'.

To prevent typos from ruining days and weeks of work you need 'Option Explicit' at the top of each code module. This forces you to declare every variable. http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis

Dear Lewis,

It is just typo mistake but with ok2close statement same event is happening message box is appearing with close button of excel and with sheet too. Kindly help on this dear. And really thanks for option explicit guide.

Hi,

The 'Option Explicit' saves a lot of headaches.

I was unable to duplicate your problem as I understand it, in the attached file. I have a command Button that closes the File.

Testing Results (all as I expected):
a. Left Click Command Button: File closes.
b. File > Close: File stays open, and get 'Use the button in sheet to close' message box.
c. Left Click on Upper Right 'X' to close Excel: File stays open, and get 'Use the button in sheet to close' message box.
d. Left Click on Upper Right 'X' to close Workbook: File stays open, and get 'Use the button in sheet to close' message box.


ThisWorkbook:

Please Login or Register  to view this content.

Module1:

Please Login or Register  to view this content.

Lewis

Dear Thanks a lot it's working I used option explicit and it started working. Thanks. Dear you are good in excel have two questions and challenging really need time from you if you can help please.

I'm glad it's working. I'll be glad to answer questions, but it's probably best if you either start a new thread so anyone can help, or send me a private message.

Lewis

Dear Lewis,

I trust you this time as you explain the things quite nice please don't mind you are best

Dear,

Question:1

How to divide amount between people ?

Situations:

i) The frequency, amount and people value is an input box and a selection box to make yes or no.
for example 3 people are there and amount is 7500 for each and frequency is 2500. We can set any frequency for instance 3500 or 2300 whatever and should be used for division and should not be broken into chunks only amount should be broken.


Now if I change people amount should be divided in full 2500 (frequency should remain same) it should not be broken. means 2500 for 1 person . 2500 for second and 2500 for third now if fourth person comes in it will be 0 but if amount increases to 10000 then fourth person will get 2500 but if there are three and amount 10000 then the first person should get or the person who has low in amount should get 2500 more means making him 5000 in total. Now selection box is there which is random selection box means if says yes it distribute excess amount to anyone in case they have same amount.


Dear i attached two pic files as these are single you open both and check


1.jpg2.jpg

Dear Lewis,

Still got no update from you on this help me dear

Dear Lewis,

Still got no update from you on amount distribution question help me dear

Dear Lewis,

Its very complicated you are best but not serving my need. Dear please please please if you can help me how you have done it so after i can ask my second question please please please help me

I answered you question as I understood it. It seemed like my answers matched your answers. If I did not do what you want, you have to show what your input is, and what your expected output is.

Please upload an Excel file instead of .jpg so I don't have to type so much.

Lewis

Dear what you have send is ok just I want to increase the frequency box means if one box is showing 2500 other 1500 and other 1000 and distribution will according to 2500 for groups in first line and according to 1500 third line and 1000 fourth line. I think dear you got it. And also kindly explain me the code you have written please i want to learn what you have coded you are genius dear

I'll start a little at a time. After you don't have any more questions we'll go on to the next part.

1. The following line erases the contents (but keeps the format) in 'Sheet1', rows 9 and 10. To remove the format too I would have used 'Clear' instead of 'ClearContents.

Please Login or Register  to view this content.

Sub starts a 'subroutine' named ClearRows9and10. Every Sub MUST end with an 'End Sub'. Functions are similar to Sub. It could have been:

Please Login or Register  to view this content.

The difference between Sub and Function is that functions usually return values. For example:

Please Login or Register  to view this content.

To be continued.

Before the next step there are a few things that have to be mastered.

1. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

2.Using the debugger.
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

You should practice 2a. thru 2f. and 2i. 2g. and 2h. are advanced future items.
Practice these things on the 'MySub' and 'Cube' code from the previous lesson.

------------------------

Building software is not about writing code. Code is just a tool used to manipulate data. Your sample jpg files defined how to move data very well. Defining the problem well is probably 80% of the solution. The next step is to write 'stub' software (software that does nothing) but has comments in it defining what we're going to do. We'll add a small amount of real code that outputs 'dummy' results in column 'A'.

For example:

Please Login or Register  to view this content.

Notice how the code is indented. Most people prefer to indent 4 spaces for each level. My personal preference is 2 spaces. Each person has his own style.

There is more than one way to identify the value in a cell (If 'Sheet1' is the active sheet, i.e. the sheet you're looking at). Just a few synonyms for the same value are:

Please Login or Register  to view this content.

Next time we'll initialize a few variables, and format the output data so it looks nicer.

Lewis

Dear Lewis,

What can I say have got no words for your help. I am really very happy to have a person like you who teach so well, I will practice the debugger. And wait for next time

Allocating Variables

1. The 'Dim' statement defines a variable by it's NAME and by the TYPE OF DATA it is. TYPE OF DATA includes strings (text), numbers without decimals (Integer and Long [Integer]), numbers with decimals (Single, Double, Currency). See the following for definitions and what values each data type can have: http://www.dummies.com/how-to/conten...ata-types.html

2. Every variable needs a 'Dim' statement to assign a 'data type' to it. You don't have to worry about the details. You need to know what TYPE you need for each piece of data you have.

Variable names must start with an upper or lower case letter. I prefer to start with lower case, and usually have one upper case letter in the name. The debugger automatically adjusts case in variables.

3. Sometimes you have a variable that will always have the same value. You can define that value as a CONSTANT.

Please Login or Register  to view this content.

4. Scope of variables. This defines how long a variable lives, literally when the variable is born and when it dies. This is an ADVANCED topic. All variables we will be using are simple variables defined inside a Sub(routine) or inside a Function. These variables live when the Sub (or function) is called, and die (cease to exist) when the last statement in the Sub (or function) is executed (usually 'End Sub' or 'End Function').

Please Login or Register  to view this content.

Look 'Sub PerformCalculationsOnRows9and10InputExcerpt() above. The 'Select' statement is needed to make sure we read the data from the correct sheet. There are many ways to make sure the correct sheet is used.

On 'Sheet1', put values in cells 'B3', 'B4', 'B5', 'B6'. Notice that if you put bad data in a cell, e.g. ('abc' or '1.1.1' in cell 'B3') you get a runtime error 13 (Type Mismatch). There are ways to prevent runtime errors, but that is also an advanced topic.
Notice that if you put '1.1' in cell 'B3', Excel assigns '1' to the value. That is because variable 'iAmountTotal' is an Long Integer type that uses whole numbers only. It will also round '1.9' up to '2'.

At the end of the routine, Cell 'A9' is formatted. You can see what kind of code you would need to format by using the macro recorder and reviewing the code that the macro recorder created. You can turn on the Macro Recorder in Excel (not VBA), using Tools > Macro > Record New Macro. You can also put a shortcut on the toolbar by having the 'Visual Basic Toolbar' visible in Excel.


Next time we'll create some dummy output data for 4 groups and output that
data to the spreadsheet.

Lewis

Dear Lewis,


Wow what a piece of explanation I am really impressed. Dear one thing I didn't get is below line.


igroups = 5 'Assign the value 5. When you cut and paste, 'igroups' will be converted to 'iGroups'


I will wait for next topic and dear at the end of all topics kindly tell me about error handling like you mentioned its advanced topic.

Really too much thanks for your such best explanations

Creating dummy output data

We'll use the ACTUAL number of groups to output dummy output data of 2500 for each group. To do this we need a LOOP. We'll do the same thing two different ways to demonstrate two of the many different types of loops. The loop we will use are the loops I use most often.

In traditional programming there are three different coding structures:
a. Sequence. We've already used sequences, to perform tasks one after another.
b. Decision. The most common decision statement is the 'if' statement. We'll discuss 'If' statements in the future.
c. Loop. A loop is used to do a repetitive task more efficiently than multiple sequence statements.

For example if we have 4 groups of data. The following SEQUENCE code would output the data in rows 9 and 10 Columns A thru D. The sequence is VERY EASY to read. It is also very easy to make TYPING MISTAKES, because the sequence is usually created using 'cut and paste'. It is very easy to have the wrong 'Cell Adress' or the wrong 'Group Number'. You also have to know ahead of time that there are EXACTLY 4 groups.

Please Login or Register  to view this content.

The following example uses a 'For' loop. Enter 4 for the number of 'Groups in Cell 'B5'. See what happens if you enter 3 groups or 0 groups or -1 groups. The output will be the same as for the sequence above. The code is a MORE DIFFICULT to read than the code for a sequence. It is a lot more flexible, because you don't have to have EXACTLY 4 groups.

Please Login or Register  to view this content.

Items of interest above:
1. The '&' is used to concatenate strings. Even though 'i' is a number, Excel knows to treat it as a 'string' in this context. If the value of i is 3, then ' "Group " & i' will become 'Group 3'.

2. The Offset statement. The offset statement is of the form 'Offset(rows to offset, columns to offset)

Please Login or Register  to view this content.

3. For loops that count up by a number other than 1.

Please Login or Register  to view this content.

4. For loops that count DOWN.

Please Login or Register  to view this content.

Next time we'll do the same thing with a WHILE loop.

Lewis

I don't know what to say but you're the best. I am really impressed and trying to learn what you're teaching dear

Looping with a WHILE loop

The following two code samples do the same thing that the FOR loop we used last time does. The WHILE loop is a more difficult to read and can sometimes cause coding (human) errors that will require Excel to be restarted. When using this kind of loop it is recommended to save your work frequently. It is also a good idea to make BACKUP files on a regular basis. Sometimes when writing macros you wander off in a bad direction, and BACKUP files may allow you to go back to a known place. The WHILE loop is MORE FLEXIBLE, because it doesn't require a known end point ahead of time. In our case we are looking for the number of groups. The conditions to exit the loop could be the value of ANY variable or the values of many variables at the same time.

Don't forget to put a value in cell 'B5' before running this macro.

Please Login or Register  to view this content.

Notice that two things were added that were not present in the FOR loop:
1. 'i = 0' to initialize the loop counter
2. 'i = i + 1' to increment the loop counter
Both of these items were done AUTOMATICALLY for us in the FOR loop.

Loops may NEVER get executed, the entire loop may be bypassed if the loop conditions are NOT MET.
Experiment with the following code by changing the values in the following statements:
a. 'For i = 4 To 1'
b. 'i = 4'
For Example:

Please Login or Register  to view this content.

Also notice that if you FORGET the statement 'i = i + 1' the value for i will remain ZERO forever and will cause
one of a programmer's nightmares, the ENDLESS LOOP.
There are several possible ways to exit an ENDLESS LOOP:
1. Press the ESC key.
2. Press CTRL-Break keys at the same time.
3. Press ALT-CTRL-DEL keys at the same time. Select 'Start Task Manager'.
Select the 'Processes' Tab if it is NOT already selected.
'Right Click' on Excel.exe. Select 'End Process'.
4. Turn computer power off (sometimes desperate times require desperate measures).

Please Login or Register  to view this content.

The WHILE loop that tests for exit conditions using the BOOLEAN trigger is more flexible, but requires a lot more work.
a. You have to check to make sure the ENTRANCE conditions into the loop are valid.
b. You have to remember to increment any counters (this is true of all WHILE loops).
c. You have to check to make sure the EXIT conditions are valid.
d. You have to make sure this NEVER becomes an endless loop.


WARNING: NEVER have two Macros with EXACTLY the same name in a file. This may cause Excel to Lock Up.
It may appear that the file is CORRUPT, but that is usually not the case.
If this ever happens:
a. Close Excel
b. Open Excel (just Excel without a file)
c. Turn Macros Off using (Tools>Macro>Security)
d. Open the file that locks Excel (will lock Excel each time it is opened)
e. Change the name of the duplicate Macro name(s) (it may be difficult to find)
f. Save the file
g. Turn Macros On using (Tools>Macro>Security)
h. Exit Excel
i. Try to open the the file using Excel.


Next time we'll go over making decisions using IF statements and SELECT CASE statements.

Lewis

Thanks a lot dear Lewis just I am confused for the usage of boolean like bNeedMore

Try to think of Boolean values as something that can have only two values. The defined two values are 'True' and 'False'. It is analogous to:
a. 'On' and 'Off'
b. 1 and 0
c. 'Not empty' (has some value) and 'Empty'

Please Login or Register  to view this content.

If statements evaluate to Boolean values 'True' or 'False'. The statement 'If Len(sValue) = 0 Then' evaluates to 'True' if the cell has NO data in it (because the length of the value is ZERO CHARACTERS), and evaluates to 'False' if there is data.

Please Login or Register  to view this content.

Since we are attempting to read data from a contiguous range, we EXIT immediately after there is no data in a cell.

Dear Lewis,

I am not very much clear for boolean I don't know why I am dumb not good like you and iRowOffset = iRowOffset + 1 is not like iRowOffset = sHeaderCell + 1

as the variable value was no where.


And also I am confused the use of Len and Trim

We'll start with a review of the items you are having problems with. Boolean is a concept that sometimes can be difficult to understand. Then we'll go through an 'If statement' at the end. Next time we'll continue with 'If Statements'.

First we'll review counting items in a loop. We'll count to 4 using a 'For Loop' and a 'While' loop. Both routines do the same thing.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

-----------------------

Now we'll use 'Len' and 'Trim'

Please Login or Register  to view this content.

------------------------
Now, another Boolean Example. Put a value in cell 'B6' of the Active sheet in the workbook (the value must be 'True' or 'False' [blank is Ok - means false]).

Please Login or Register  to view this content.

Finally, a slightly more advanced version of the previous example, to ELIMINATE the runtime error if there is a BAD value in cell 'B6'. We will define anything other than 'True' to mean 'False'. Do not worry if you don't understand this example. This is to show you that it is POSSIBLE to protect against bad USER INPUT.

Please Login or Register  to view this content.

Lewis

Dear

Boolean concept is cleared nearly thanks a lot but on error resume next or on error goto zero or if err.number are not very well cleared. And rest trim and len are cleared almost

I'm glad you nearly understand most of the concepts. The important thing is that you know they exist. 'on error resume next' and 'on error goto zero' and 'err.number' are advanced items that you should not worry about now.

Today we'll talk about Excel statements in general. I'm pushing IF statements back again until next time.


Excel statements
a. One statement per line
b. More than one statement per line (to save space). The colon ':' is used to do this between statements.
c. Continuation lines (One statement takes many lines). The underscore '_' is used to indicate that the current statement is continued on the next line.

a. One statement per line:

Please Login or Register  to view this content.

b. More than one statement per line (to save space):

Please Login or Register  to view this content.

c. Continuation lines (One statement takes many lines):

Please Login or Register  to view this content.

Lewis

Dear Lewis,

I got it you are the best. I got it how to use if statement thanks dear

IF statements and SELECT CASE statements

Last time we looked at the number of statements per line and simple IF statements. This time we'll look at both these items to demonstrate different ways of doing the exact same thing, and my preferred way.

Simple IF statement revisited:

Please Login or Register  to view this content.

IF THEN ELSE statement:

Please Login or Register  to view this content.

IF THEN ELSE IF examples:

Please Login or Register  to view this content.

Select CASE examples (very similar to IF-THEN-ELSE-IF). I like to use SELECT CASE statements whenever I can, because I find them a lot easier to read than If statements. 'Select Case i' makes a decision based on the value of 'i'. So 'Case 3' is equivalent to saying 'if i = 3 then'.

Please Login or Register  to view this content.

Next time we'll go back to the original worksheet, and work on how to do the calculations.


Lewis

Dear Lewis,

Really helpful. You're best. Now i am interested in Case Statements. Really want in-depth training of it dear.

Here is more details about Case Statements. The attached file has detail code examples about a few 'Case Statement' constructions.

The following are excerpts from the file.


This assigns a color to certain values.
1. Notice how all possible values are anticipated. In real life this code should NEVER see a value LESS THAN ZERO or GREATER THAN 100. Testing for those values is known as DEFENSIVE PROGRAMMING.
2. Notice that ALL VALUES are anticipated. There are no gaps in the range of values.

Please Login or Register  to view this content.

This tests the validity of a line thickness input value. The purpose is to prevent processing if there is a bad value input. All items that start with xl are numerical constants defined by Microsoft. We don't care what the EXACT numerical value is. We can refer to the value (e.g. xlThick). It is sometimes difficult to find a list of these values. These values were found at: http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

Please Login or Register  to view this content.

Here is another way to write the same code. I prefer the other method, because I think it is easier to read, and less prone to mistakes.

Please Login or Register  to view this content.

The last example uses text instead of numbers:

Please Login or Register  to view this content.

Next time we'll go over Calculations for your project, unless you have something else in mind.

Lewis

You're really very helpful. Thanks Lewis I am really impressed by your skills. Please make me expert too please. I am little confused for case statement. If you can help me on this. hOW LONG CASE STATEMENTS can go. Means how long code I can use dear.

hOW LONG CASE STATEMENTS can go. Means how long code I can use

I don't know what the limitation is, but it is probably larger than you or I will ever use. The best way to learn is to try your own, after you know the basics.

Lewis

Thanks a lot I tried it but dear I was trying to make one work with case statements. I have one amount I want to divide it in 4 equal halves but don't want decimal values. if there are decimal values then the excess of decimal will go to fourth person. For example. I have 83 rupees I divide it in 4 then i get 20.75. Now we finish decimal in such a way that other three will get 20 share and 4th person will get 23. But this I am confused help me out this with Case Statement dear

Hi,

I didn't need a 'Case Statement' to solve your problem, just some algebra.

Please Login or Register  to view this content.

Today we will focus on the original worksheet, and work on how to do the calculations.
The first step (either on paper or mentally as your skills improve) is to walk through
what you want to do sequentially with words. In the walkthrough we assume everything is going
to work correctly, and we don't worry about problems or errors YET. There is no sofware involved.

We'll go back to the division example in Post #13 in this thread, and use the following data:

Please Login or Register  to view this content.

The answers in row 9 and 10 are:

Please Login or Register  to view this content.

Walkthrough Steps:
1. Get the input values.
2. Output the Header for Group 1.
3. Output the Header for Group 2.
4. Output the Header for Group 3.
5. Allocate 2500 to Group 1.
6. Allocate 2500 to Group 2.
7. Allocate 2500 to Group 3.
8. Allocate 2500 to Group 1.
9. Allocate 100 to Group 2.
10. Stop

Now we convert the Walkthrough words to code that works.
We keep it as simple as possible. We don't worry about removing
previous data or activating the correct worksheet. We do all that manually in the worksheet.

Please Login or Register  to view this content.

Notice how we just solved one specific problem, with one specific set of input numbers.
Since it looks like we're doing something repetitive, next time we'll try to adapt some kind of loop
to the problem.

Lewis

Dear Lewis,

Thanks for your wonderful explanation hope one day I will be somewhat expert like you. You're really nice and expert. I got the above you have done are all manual distributions am I right dear?

I got the above you have done are all manual distributions am I right dear?

Yes. There are files to download from post #6 and post #13, but I'm sure you have seen them.

Dear I have a question that if I Right click on a cell there should appear a Data Validation Table with Menus and Sub Menus. How this can be done? Kindly help?

I used one code

( Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Application.Dialogs(xlDialogDataValidation).Show
End Sub )


but this showing just dialog of data validation but I want menus and sub menus so to just select any input or data for that cell and if I want this in specific columns then what we have to do.

Hi,

I don't understand your question, since I don't know what you mean by ' Data Validation Table with Menus and Sub Menus'.

The following link may help you: http://tim.webanalyticsdemystified.com/?p=1302

Dear,

It's like that when you right click a list will appear to choose from and list should have sub menus too .For Example I right click menu will appear will education selection and sub menu of education there are Chemistry, Business Administration etc. I hope you understand only this I need

I'm sorry, but I can't help you because I'm not familiar with what you need. I suggest you start a new thread with your question.

Dear you are an expert I beleive just if you can say I can explain you more. Please guide me dear

I can't guide you, because I am not familiar with that kind of construction. I have not used or seen what you are talking about.

K dear no issues can you help me in that If I want to get date in specific columns by button press and if I press in any other column nothing happens or message appears. Can you help me on this dear.