Detecting If Only One Row Is Selected, Have Problem With The Cntrl Key

Jul 7, 2009

I want to enable button on the Menu and Toolbar if User is selected entire row (only one row). If it is selected some Range or more than one Row, the button must be disabled. I'm using this to detect what is the user selection:

View 2 Replies


ADVERTISEMENT

How To Calc Stdev But To Exclude 0's. Prefer No Array Cntrl + Shift + Enter Calcs

Dec 12, 2008

I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.

View 8 Replies View Related

Detecting Top Of Page

Feb 5, 2009

Is there a way of detecting, when printing, that a cell is at the top of the page?

I am not talking about the automatic "Rows to repeat at top" option on Page Setup; I have programmatically suppressed repeating information in Column A but I want to repeat that information if the column is fed to a new page.

View 2 Replies View Related

Detecting Same Value In 3 Cells

Mar 11, 2009

Would like to detect same value in 3 cells.

Q 16, R 16, S 16

If there is a same value appearing in two of the three cells.

View 11 Replies View Related

Detecting The Last Column

Mar 15, 2007

I've written a macro to copy data from a series of columns on one sheet and past it on another sheet in a specific way. However, what I really want to do, is write the macro so it automatically detects what the last column is, either by defining a range consisting of the last column, or by writing "Last Column" in one of the cells of the column, and having the macro detect it.

Dim i As Variant
For i = 5 To 56
' What I want to do is have the second number be the last column, instead of 56.
Sheets("MASTER").Select
Selection. AutoFilter Field:=i, Criteria1:=">0", Operator:=xlAnd
Range("Basic_Data").Copy
Sheets(" Consolidate").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 4).Range("A1").Select
Sheets("MASTER").Select...........................

View 5 Replies View Related

Detecting Formulas Vs. Constants

Oct 1, 2006

In my company we have many spreadsheets with sections that contain formulas, which may be overwritten with "hard coding", manually entered constants, in certain situations.

Is there a way I can detect the differences between the two types of cells? I would like to setup conditional formatting that would highlight constants in a table that would generally only contains formulas.

View 14 Replies View Related

VBA Not Detecting Email Address?

Jun 20, 2014

It worked well at the time and continues to work well.

The problem I have is that I've copied it for use in a new workbook and for some reason it's not picking up email addresses and storing them in the string "stemails", so that when the email is created there are no addressees. Column BA does have email addresses in it however.

[Code] .....

View 2 Replies View Related

VBA Detecting Color Change

Feb 10, 2012

Using Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) I can execute code when values in the cell change, but it can't check if the foreground or background color changes. What event can detect this? If no event can, is there any way at all?

Found this, I will look into this loop: Capturing cell color change in VBA

View 5 Replies View Related

Detecting CTRL Key Or ALT Is Pressed

Sep 29, 2013

How could I detect if the CTRL (Control) or ALT key has been pressed. I want to execute a macro when either one is press over a given cell.

View 3 Replies View Related

Detecting And Separating Integer

Dec 1, 2008

A1 : Style 998 45702B WRAP DRESS 1 0 0 0 1

Try to accomplish:

I would like to remove the "1" in between "Dress" and "0"

in other word remove integer from string but starting from right only.

B1 = should contain 1

View 9 Replies View Related

Detecting Non Contiguous Row Selections

Dec 22, 2008

I execute custom Insert and Delete macros whenever when the user has selected a single contiguous range of rows to insert or delete.

But I don't know how to detect if the user has selected multiple, non-contiguous rows. Is there a way to detect this situation, and if so, is there a way to "loop" through the individual selections?

View 9 Replies View Related

Detecting What Object Was Clicked

Jul 22, 2006

I've been working on a visualizer for large data sets and I'm trying to create a flexible interface. What I am trying to do is populate a sheet with objects(rectangles, triangles, etc.) and linking them all to the same macro. The macro would detect what had clicked it and read the alternative text which would hold the command procedure for that object. I've seen how people detect cursor position but I have not come across anything to detect the object that was clicked. The code I've found (in another post) is as follows:

Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Type POINTAPI
x As Long
y As Long
End Type

Sub CurosrXY_Pixels()

Dim lngStatus As Long
Dim typWhere As POINTAPI

lngStatus = GetCursorPos(typWhere)
MsgBox "x: " & typWhere.x & Chr(13) & "y: " & typWhere.y, vbInformation, "Pixels"
End Sub

This works but I can't figure out how to detect the object it clicked. I was hoping to use

ClickedObject = ActiveWindow.rangefrompoint(typeWhere.x, typeWhere.y)
Command = ClickedObject.AlternativeText

but all I get is errors.

View 2 Replies View Related

Sensing/Detecting Format Changes

Aug 16, 2006

I need to code a procedure which detects when a cell has changed color (I cannot assume where the cell is).

I thought I might be able to use the worksheet_change procedure, but this only 'reacts' when the cell contents change. Can anyone point me in the direction of the right procedure to implement.

View 9 Replies View Related

Detecting Existence Of Worksheet

Feb 14, 2007

I have a macro tht deletes sheets from my workbook. How do I check if these sheets exist before deleteing them? Thus so that

Worksheets("MySheetName").Delete

Does not generate an error if "MySheetName" doesnt exist?

View 2 Replies View Related

Detecting When A Worksheet Is Deleted

Mar 20, 2007

I am working on a workbook in which you can click a button on an index page to create a new sheet in the workbook with the name "meeting" and the number meeting that it is. The new sheet is then added to the index page. I am looking for a way to detect when a sheet is deleted so I can update my index page accordingly. I need to be able to know when a meeting is deleted so I can rename all meetings after that meeting and then update my index.

View 7 Replies View Related

Detecting Page Breaks

Mar 20, 2007

I want a macro to assess whether there's a page break before column P, if so, remove it and continue, if not... continue.

View 7 Replies View Related

Grading Spreadsheet - Detecting Array

Mar 20, 2007

I have created a table with the values of each letter (A = 4, A- = 3.7, B+ = 3.3, through F)

I would like to have a points column which multiplies my letter grade (references a value from the table) by the units the grade is worth.

What function can I use to compare a letter grade with a value I've set.

Example:

Grade: B+
Units: 4
GPA Points Earned: = 3.3 * 4

View 14 Replies View Related

Detecting Size Of Data On Clipboard

Mar 22, 2010

I am trying to write a macro to paste the data that has been copied onto the clipboard into my worksheet. However I want to paste it in different ways depending on what data is there.

This is what I have so far:

Sub ImportTissue()
Sheets("Tissue").Select
If "dimensions of clipboard data are 5 cells by 5 cells" Then

Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[Code] .........

View 8 Replies View Related

Detecting Change In Spreadsheet - Which Cell

Apr 13, 2012

How to detect a change in a spreadsheet, but how do i etermine on which cell the change happened?

Basically, I need something like this, if in the range of b1:b30 a cell is changed by the user, let say B??, then change or modify (via a subroutine) cell A??.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
' detect which cell in the range b1:b30 changed
' then modify the cell to the right (or left) of the changed cell
'
End Sub

View 4 Replies View Related

Macro - Code Not Detecting All Subfolders

Jan 9, 2013

I'm using this piece of code to go through Folders and their Subfolders. It works quite well in going through the first folder and its sub's but does not seem to generate a full list.

I am looking for specific files in these folders, and depending in what folder I place the file, it may or may not detect it. I require the code to go at least 3 levels deep (sometimes more).

FolderName = "C: est folder"
ProcessFiles FolderName, "*.xls"

Code:
'~~> This function was taken from
'~~> http://www.vbaexpress.com/kb/getarticle.php?kb_id=245
Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String, strFolders() As String
Dim i As Long, iFolderCount As Long

[Code] ......

View 3 Replies View Related

Autofill Not Detecting Range Pattern?

Apr 10, 2013

I have the following series of formulas consecutively in a single column:

=IF(A3=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A4=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
.
.
.
=IF(A50=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A51=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
=IF(A52=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
.
.
.

Effectively, I am comparing the values in each set of 47 to determine which one is the smallest. The smallest entry will get a YES label next to it and all others will get a NO. The problem I am having is the the autofill feature is not correctly detecting the pattern, even if I manually fill in the first 49 entries (shown above). If I hilight all of the above entries and autofill one down, I get the following:

=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H53:H100),H53:H100,0),1)),"YES","NO")

It should be:

=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")

Basically I need the H:H: reference to stay constant, but only for 47 cells at a time. After that, both the lower and upper bound should increase by 47. Is there any way to indicate this to Excel? Manually typing in the new formula every 47 entries isn't practical because the spreadsheet has thousands of entries. How I can accomplish this using a single formula and autofill?

View 2 Replies View Related

Detecting If Floppy Disk Is In Drive

Aug 11, 2007

I'm trying to make it so that a backup copy of my file is written to floppy when the file is saved (but not with SaveAs). So far, I have the following code, which if the floppy is in the drive works fine, and doesn't error out if the disk is not in the drive. But it doesn't make sure that a backup copy actually got made. How would I check to see if the disk is in the drive and loop somehow until it was so that the file got saved to floppy?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Application.DisplayAlerts = False
If Not (SaveAsUI) Then
MsgBox "Please Insert Floppy Disk in Drive A:"
ThisWorkbook.SaveCopyAs "A:" & ThisWorkbook.Name
End If
Application.DisplayAlerts = True
End Sub

View 9 Replies View Related

Detecting The Last Filled In Cell In A Column

May 4, 2009

Is there any formula or macro that can be used to detect the value of the last filled in cell in a column or row? For example, I fill in column B each day with a new numerical variable, so on day one B1 = 100, on day 2 B2 = 200, on day 3 B3 = 150 and so on. I would like the last variable entered in column B to appear in, for example, cell A1. Is there any way to do that?

View 9 Replies View Related

Detecting Duplicate Cells Using Info From The List

Feb 5, 2014

I have a sheet containing lots of information (lists of flight information to and from all countries). So entries are duplicated in the entire sheet. For example LITHUANIA, USA, CHINA... appears lots of times. I only need to highlight particular countries.

I would just use conditional formatting, but maybe there is a quicker way to do that?

So there is a list of 50 countries in one sheet and in another sheet I got all the info. I need those 50 countries from the list to be highlighted in the info sheet (sheet has many columns and rows).

View 6 Replies View Related

Detecting Change Of Pages In Multipage Form

Oct 21, 2011

Is there a way to detect a change of pages in a Multipage form, i.e. is there a change event that is triggered when a user goes from page 1 to page 2? If there is, what does the code look like?

View 5 Replies View Related

Excel 2010 :: Detecting When Chart Is Deleted

May 30, 2013

I need to detect when some Chart is being deleted. This is part of an XL2010 VSTO addin I an developping. I could not find any event matching my need.

Did I miss some event somewhere? Would you know a (simple) method to detect when some Chart is being deleted by the user?

View 1 Replies View Related

Detecting If A Cell In Range Of Cells Has Been Modified

Jun 11, 2013

I want to detect if a cell within a range of cell has been modified

A range of cell is named "ChartData" =Data_In_Out!$E$15:$E$29

I understand how to react for a single cell, but not with a group of cells

Code:

Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = Range("LastBarString").Address Then
Application.EnableEvents = False
...
...
end if

View 2 Replies View Related

Excel 2007 :: Using Formula For Detecting Blank Cell In Closed Workbook

Jan 27, 2012

Anyone know of a way to detect if a cell is blank in a closed workbook?

Here's what I'm trying to do:

=IF(ISBLANK(closed workbook cell reference),NA(),closed workbook cell reference)

also tried:

=IF(closed workbook cell reference="",NA(),closed workbook cell reference)

Neither one worked. I'm using Excel 2007.

View 8 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)

[Code]....

When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Elseif Statement In Vba: If The Selected Cell Falls Between 1/01/06 And 31/01/06 Then Jan Would Be Selected

Oct 10, 2006

Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.

Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved