Free Microsoft Excel 2013 Quick Reference

VBA Worksheet_BeforeDoubleClick

HI All,

Can any one help me out understand how this event operates. I am looking to have a macro That kicks in when cells within a certain column are double clicked. Basically I am looking to expand or collapse a grouping I have on the adjacent two columns to right.

My Initial question is will this macro kick in once the user has doubleclicked on a range or before? Also is it possible to assign a macro like this to a set column as aposed to an entire worksheet and have multiple macro's to expand/collapse multiple groupings individually?


Post your answer or comment

comments powered by Disqus
Quick VBA Worksheet Change Event or Selection Question:

I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.

In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.

The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.

So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?

Example:

If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above) into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.

I thank you for your patience and assistance!

Hi,

Can anyone tell me how to generate a variable VBA worksheet name so I
can search through the set up sheets.

Basically the VBA names of my sheets are S1 to S10 allowing me to use
the

S1.Activate etc...

How can I set up the Sheet name to be something like:

wrksheetnum = (loop through worksheet numbers)
wrksheetname = "S" & wrksheetnum

wrksheetname.activate

loop etc...

How do I get the wrksheetname bit to work?

Thanks
David

I have a VBA problem that I feel someone good at syntax will have no trouble with.

The problem: I have the code creating a tab for each unique "employee number" it finds in a raw data worksheet. The macro then populates these worksheets with the data from the raw data worksheet specific to that employee.

Later a macro creates a tab with the employee number followed by the word “points”, where their “quality scores” are calculated and displayed. I am trying to use a COUNTIF statement in the “employee points” worksheet to count how many times a value in a column in the “employee number” (aka data) worksheet violates an upper limit. In order to match the correct employee data to the correct employee points worksheet I am using the list of unique employee numbers I created in the raw data worksheet.

I can step through the code all the way to the end with it updating the screen correctly, but I can't get it to pull the range specified in the COUNTIF. Any thoughts would be greatly appreciated.

Thanks for your time.
Geoff

Please see code below: I think it makes more sense that what I just typed:


	VB:
	
 Macro2() 
     '
     
    Sheets("raw data").Select 
    If Range("AB2").Value  "NA" Then 
         
         ' this variable is ("employee numner" + Points) and it is used to find the right worksheet to insert values into
        Dim employee1 As String 
        employee1 = Range("AB2") 
         
         ' this variable is ("employee number") and it is used to find the right worksheet to pull data from to populate the
("employee number" + points worksheet.)
        Dim employeedat1 As String 
        employeedat1 = Range("AA2") 
         
         ' stuck, can't get the COUNTIF statement to pull data from the range specified in the worksheets(employeedat1) -
         ' Need to figure out how to specify a range from another worksheet in the COUNTIF statement.
         
        Sheets(employee1).Select 
        Range("D3").Select 
        ActiveCell.FormulaR1C1 = "=COUNTIF([COLOR=red]'worksheets(employeedat1)'!k2:k4000[/COLOR], ""

Hi,

I have created a function in VBA to use in my excel worksheets. It is an "if" function referring to a number on a different sheet i.e. =if(sheet2!H15=1,do this,do that). It works fine except that it won't recalculate when I change the number on the other sheet. I have to press shift + F9 in order for it to give me the correct value. I have tried application.volatile but it doesn;t have any effect. Any suggestions?

Thanks

Adam

Hi,
Is there analysis tool or a set of steps to follow in order to conduct quick and effeciant analysis for a worksheet that has lots of VBA code.

Thanks,

Does anyone know how could I write a code in vba about searching the worksheets names ?

For example I want to look that if a worksheet name "x" exists in my workbook or not how could I do this.

Thanks,
Semih

How do I unprotect and reprotect a worksheet that is password protected using VBA code. I tried the recorder and got the following:

ActiveSheet.Unprotect
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

Obviously it ignored my password. I would like to keep a password on this worksheet so I need to know how to reference it in the code (so I can unprotect the sheet, run some procedures, and reprotect the sheet with the same password).

I have a series of worksheets containing VBA code. Is there a way to auto test the data? The worksheet screen shows the correct data but the cell shows the formula. I know of no tool that will read the data on the worksheet itself.

Hi, I attached my workbook so you guys can easily take a look.
In this workbook I have 2 hidden worksheets.

To start off, you click the Add Week button, put in a date (mm-dd-yy), that brings up the sheet thats named with the date you just put in the text box. (Which happens to be placed in the wrong spot) In that sheet that appears theres a button to "Delete last" which is suposed to delete the last sheet in the worksheet but the new sheet gets placed between the existing charts. Thats my first problem.

Next, when you hit the delete button, my VBA code is set to make one of the hidden sheets visible so it can delete one of the rows.

Its fine deleteing the row, but when I go back and select this sheet again I get an error with the selection. 

even though I have the exact same line of code just before and it doesnt give me an error.

Heres a snipet of the code but you guys can take a look at the actual file for the full thing.

    Sheets("Total WO
Table").Visible = True
    Worksheets("Total WO Table").Select
    Sheets("Total WO Table").Move Before:=Sheets(4)
    
    Worksheets(Worksheets.Count).Select
    nameD = ActiveSheet.name                 'Name of last sheet as variable
    
    ActiveWindow.SelectedSheets.Delete      'Deletes last sheet
    
    
    Worksheets("Total WO Table").Select   '<--- This is where error occurs.


Hey,

Two small question:

1. How can I make sure a user can't delete a worksheet. Since I
constantly make new sheets and delete hem myself this should be in VBA
and must not interrupt with my own deleten en making worksheets. So it
should only be in Excel hisself.

2. I have a range of cells in excel that the user can see, but I want
him to prevent scrolling down. Can I do that in VBA and how

thx in advance

Dieter

Group,
On a worksheet I can subtract in cell =B2 - B1 in cell B3. How would I do the same operation in VBA?

Tony

Hi folks,
Im just looking for some VBA code that will automatically create a wooksheet if a cell has been populated and name the worksheet with the values of that cell.

Any ideas on how to do this would be apreciated.
Cheers

Tony

I have a VBA programming question. I have this macro that creates about 100
sheets every time I run it. Each workbook contains a maximum of 255
worksheets. My problem is if I make a mistake and I want to start all over
again by deleting all 100 sheets. When I rerun the macro it will not realize
that they are new worksheets; i.e., the worksheet counter will start from
101-200 instead of 1-100 again. If I make another mistake, I will need to
run the macro the 3rd time. The program will then freeze since I've already
exceed the 255 sheets limit during my 3rd execution.

My question is: Is there are way to reset the worksheet counter to 0 every
time I run the macro so I don't have this problem?

Thanks in advance,
Ellen

I have been running an excel Macro for a while now and keep running into the same issue with it. After multiple runs of the macro on a worksheet the performance eventually slows down dramatically. The code runs the Excel solver function on a bunch of different design points. when first created it will run 600 points in 3-4 minutes. Once it slows down it takes upwards of 30 minutes. The slowdown doesn't happen gradually. Rather one day I will run the code and it will be fast and the next day it will be slow.

I believe it is a worksheet issue more than a VBA issue because I can re-create the sheet (even within the same workbook) and it will run fast again. If I copy the cells and VBA code directly to a new sheet nothing changes. However, copy and paste special the cells as values, copy the VBA code, and manually re-insert the formulas in the appropriate cells it will run fast again for a while.

Any help that would eliminate the need for me to copy to a new sheet would be much appreciated.

Thanks
DKlutzke

Sub Macro_2_Pass_Fuel_Solver()

    Dim i As Long
    Application.ScreenUpdating = False
    i = 6
    Do Until IsEmpty(Range("A" & i))

        SolverReset

        SolverAdd CellRef:=Range("I" & i), Relation:=1, FormulaText:="$I$2" 'sets maximum fuel vol
tank 1
        SolverAdd CellRef:=Range("J" & i), Relation:=1, FormulaText:="$J$2" 'sets maximum fuel vol
tank 2
        SolverAdd CellRef:=Range("K" & i), Relation:=1, FormulaText:="$K$2" 'sets maximum fuel vol
tank 3
        SolverAdd CellRef:=Range("I" & i), Relation:=3, FormulaText:="$I$1" 'sets Min fuel vol tank
1
        SolverAdd CellRef:=Range("J" & i), Relation:=3, FormulaText:="$J$1" 'sets Min fuel vol tank
2
        SolverAdd CellRef:=Range("K" & i), Relation:=3, FormulaText:="$K$1" 'sets Min fuel vol tank
3
        SolverAdd CellRef:=Range("W" & i), Relation:=2, FormulaText:=0 'says sum of three tanks must equal
total fuel vol
        SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.000001, AssumeLinear:=False, StepThru:=False,
Estimates:=1, Derivatives:=1, SearchOption:=1, IntTolerance:=2, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
        SolverOk SetCell:=Range("v" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("I"
& i & ":J" & i & ":K" & i)
        
        SolverSolve userFinish:=True
        i = i + 1
    Loop
    
    Application.ScreenUpdating = True
End Sub


For some reason i dont know why this doesnt work

dim int as integer
int = Application.WorksheetFunction.Count("b10:eo10")

when i do the count function in the worksheet i get a result of 68 but when i do it with code the result comes up 1

Hi

I hope someone can help. I have the following line in VBA which works.


	VB:
	
85") / Total 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to replace the Range("DN:DN") as this might not always be the column I'm looking for.

I have a variable called Score with which I can put the column number, 118, or cell reference, in this case $DN$1 in it.

What I can't do is work the variable into the VBA Line in the correct syntax.

Can anyone help please?

Thanks

Mike

Hi,

I'm trying to make some code execute when either of two specific cells are changed. (F3 and I3).

I'm using some help notes from this website: http://www.ozgrid.com/VBA/run-macros-change.htm
I pasted the following code into the VBA worksheet that I want the code to apply to:


	VB:
	
 Range) 
     'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub 
     
    If Target.Address = "$A$1" Then 
         'Ensure target is a number before multiplying by 2
        If IsNumeric(Target) Then 
             'Stop any possible runtime errors and halting code
            On Error Resume Next 
             'Turn off ALL events so the Target * 2 does not _
            put the code into a loop. 
            Application.EnableEvents = False 
            Target = Target * 2 
             'Turn events back on
            Application.EnableEvents = True 
             'Allow run time errors again
            On Error Goto 0 
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
At this stage I simply want to see the code triggered so that I can see how it works. I will then make my required changes.

For some reason the code does not run when I change a cell in the main sheet.

Can anyone tell me why?

I am using Office XP.

I want to activate a worksheet in a workbook. The user will input the name of the worksheet to be activated. Could someone please help me

on a worksheet change event routine, I am opening up a userform that is linked to the sheet...if the user makes a change, than the sheet is updated and errors out since the userform is already open...

How do I check first in the routine to see if the userform is up and exit the routine if that is true?

Thanks

Hello

i am not very clear about the use of "worksheetfunction". Please help me with the following example.

I have a set of numbers in the range(b44:d44) and another set in range(b45:d45). In cell b46 I wan to have the formula=round((b45/b44),2) and copy and paste this formula in cells c46 and d46. How to implement this using worksheet function

Hello,

i put a command button on the first sheet of a template workbook and it is my intention to put data from a web query to another sheet in the same workbook when i press the command button created on sheet1. the code i have is as follows:

With ThisWorkbook.Worksheets("Sheet2").QueryTables.Add(Connection:= _
"URL;

Hi all,

Excel 97 Win 2000
The following code is in the Sheet code (not a module or in This WorkBook). The msgbox stuff is there for debug purposes.
The code works the first time the cell is changed but after that the value for four5 remains the same. I need to evaluate the target cell each time it is changed. Any help is greatly appreciated. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim tot
Dim four5

For Each cell In Target
tot = (Worksheets("My CR Breakdown").Range("E1").Value) * (Worksheets("Ford Breakdown").Range("M59").Value)

Select Case tot

Case Is > 5000000
ax = MsgBox(tot, vbYesNo, "l")

Case Else
Application.EnableEvents = False
four5 = ((Worksheets("My CR Breakdown").Range("E99").Value) / 0.55) * (Worksheets("My CR Breakdown").Range("E1").Value)
If (Worksheets("Ford Breakdown").Range("M59").Value) < four5 Then
Worksheets("Ford Breakdown").Range("E59").Value = "NOT FOR CUSTOMER DISTRIBUTION"
Worksheets("Ford Breakdown").Range("E60").Value = "ANNUAL SALE IS LESS THAN $250,000 AND LESS THAN 45% GM"
End If
'Case Else
MsgBox four5
Application.EnableEvents = True
End Select

Next cell

tot = ""
four = ""

End Sub

I have Conditional Formatting applied to many cells, and I need to secure them through my VBA worksheet protection, but need to allow users to apply simple cell background color as necessay. If I include [I]AllowFormattingCells:=true[I] in my VBA Protect code, users have access to all format functions, including Conditional Formatting. Is there a way to "Git 'R Done" with VBA while Protect (with password) is active?

Hope someone can help,
powerjak

Hello,

I am wanting to create a macro to allow me to have tabs grouped (aka multiselected) together when I run the macro and then to have the macro identify and store the names of each tab in the grouped tabs. Assume that anywhere between 1 and 10 tabs might be grouped.

I was wondering if anywhere could recommend how to tweak the code to do what I would like to do.

Code:
Sub WorksheetCollection
Dim ws As Worksheet
Dim NumberOfTabs: NumberOfTabs = 0
Dim ws1 As Worksheet   'should I declare 10 worksheet variables? Is there a simpler way?
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Dim ws7 As Worksheet
Dim ws8 As Worksheet
Dim ws9 As Worksheet
Dim ws10 As Worksheet

Dim OrigSelectedTabs As Collection  'is collection appropriate, or is there a better type of object to be set equal to
ActiveWindow.SelectedSheets?
    
NumberOfTabs = ActiveWindow.SelectedSheets.Count
Set OrigSelectedTabs = ActiveWindow.SelectedSheets    'this chokes up the macro

For Each ws In ActiveWindow.SelectedSheets  'this will cycle through all selected tabs
x = ws.name  'here I would like a simple way to identify the name of each tab and store for later use
Next
End Sub
Thank you in advance for considering this.

Jared

I do not see why this is returning a mismatch error. WsS is the referenced worksheet. It compiles.

WsS.Range("A2:A" & lLrwS).Value = Application.WorksheetFunction.Trim(WsS.Range("A2:A" & lLrwS).Value)


No luck finding an answer? You could always try Google.