Detect Add-in

Apr 25, 2008

I need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"

The code I have so far is:


Sub Amarta_Specs()
Dim a

'ActiveSheet.Shapes("Button 1").Delete
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"

Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")

On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"

Error:
Cells(2, 3) = "No"

ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"

End Sub

ADVERTISEMENT

Detect Changes In A Row

May 4, 2009

I am trying to create a code in the Worksheet_change event. I have hundreds of rows of data in a sheet. Each row of data is one unique project. What I am trying to do is figure out if any cell in a specific row has changed, and place a date at the end of that row idicating the date of the change.

View 9 Replies View Related

Detect MsgBox

May 23, 2007

How to detect msgbox pup up event.

Does any function like msgbox.activate=true or somrthing can used.

I want to avoid msgbox show up and close msgbox automaticly.

I know

application.displayalters=False

But it's seem not to apply to msgbox close.

View 9 Replies View Related

Detect If Workbook Is Already Open

Jan 26, 2007

I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours,

VB:
Dim wBook As Workbook
'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls")
If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and
cannot be updated. Please try again when the workbook Is available", _
vbCritical, "Committed Request Edit")
Goto EndUpdate
End If

View 5 Replies View Related

Function Or Sub To Detect Doubles

Jun 19, 2007

I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.

Has someone an idea or is there a build in function to handle this?

View 10 Replies View Related

Detect Cells With Links

Jul 27, 2006

How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.

I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links

View 10 Replies View Related

VBA Detect Duplicates - Keep Only Newest Ones

Feb 23, 2012

I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.

View 1 Replies View Related

Detect Links Within A Workbook?

Jul 20, 2012

I have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.

Is there anyway to check if a range is being used as a link in another sheet.

Eg.

In sheet1 , A1 i have a value Apple.

In sheet2, A1 I have linked it to sheet1 A1.

is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.

View 1 Replies View Related

Detect If User Prints

Dec 12, 2006

Is there anyway to detect if the user has just printed. I have code which transfer data from the sheet to an access file when the user prints using the button I placed on the sheet, unfortunately some users are using the print button on the toolbar.

View 9 Replies View Related

Detect Key Press Upon Startup

May 27, 2007

Is there some way to detect that a key (specific or any) is being pressed when an Excel workbook is being opened to activate optional precessing in the macro code?

View 9 Replies View Related

Detect End Of Data In Row And Move To Next

Apr 12, 2009

if i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example

Array Data

Colmn A B C D E
Row1: 3 1 4 6 8
Row2: 6 7 8 9 10
Row3: 4 5 1 2 8
Row4: 6 4 3 2 1
Row5: 2 4 1 2 2

OutPut Format

Colmn F
Row 1: Age is 3
Row 2: Always Same - Note this appears even though not part of data
Row 3: Year is 1
Row 4: Time is 4
Row 5: Hour is 6
Row 6: Minute is 8
Row 7: !
!Not Part of Output Comment: Now row 1 data has ended. Move to Row 2!
Row 8: Age is 6
Row 9: Always Same - Note this appears even though not part of data
Row 10: Year is 7
Row 11: Time is 8
Row 12: Hour is 9
Row 13: Minute is 10
!
etc
etc
etc

also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.

View 9 Replies View Related

Detect When Web Query Has Updated

Aug 17, 2006

I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data. I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.

I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?). I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.

(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or

(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?

View 2 Replies View Related

Detect Changing Color

Feb 8, 2007

How can I with vba detect when someone changes the color of the font in a cell

View 5 Replies View Related

Detect Blank Cell Value

Apr 12, 2007

I am trying to detect if a user leaves a cell empty. I have tried If statements like:

If Target.Value = "" Then

and

If (IsNull(Target.Value) Then

The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.

View 5 Replies View Related

Detect/Prevent Text

Jun 6, 2007

In column A i have a range of dates
In column B i have a range of descriptions
In column C i have formula which calculates an interest figure based on the date entered in column A and a date entered in a cell elsewhere. It also makes the cell display a couple of different strings of text if conditions are not met e.g. It displays "too early" if the date entered is more than 4 years before the date entered in the cell elsewhere

The problem that im having is this:

When i try to calculate column C i want it to throw an error if there is anything but a numeric value in the column. But it is obviously considering the cells in column C with for e.g. "too early" as a "0". I have tried all sorts of formula to do this to no avail, IFTEXT, IFNUMBER, ISTEXT, ISNONTEXT etc etc.

View 7 Replies View Related

Detect Operating System

Mar 8, 2008

how can I detect the OS of the computer a sheet is running on and launch a macro accordingly?

I have built a calculator that utilizes an embedded wave file but doesn't work correctly with Vista. With Vista it actually causes WMP to be launched instead of just playing the file. So I was hoping to be able to skip the sound on Vista machines but play for all other OS's.

View 3 Replies View Related

How To Detect Blank Rows And Then Delete

Apr 18, 2013

I need to detect blank rows and then delete them but for the formula not be be affected

In the example attached there are 4 components but i may only need to use 3 so row 4 would be blank

i need to find the blank rows and delete them and for the formula in cells G12:H14 to be update as necessary

at the moment if i delete the rows i get a REF# in place of the cell which has been deleted

i have tried this on a simple formula and when you delete a row the formula changes as required

View 5 Replies View Related

Macro To Detect Cell Range

Aug 25, 2007

I am currently using the following macro to input a formula into my worksheet:

View 12 Replies View Related

Parse A String And Detect ALT-ENTER?

Jul 16, 2009

I will have a cell that has company names in it and I need to parse each name an then do some work with them but when I started this project I was told each company will be seperated by some number of spaces.

Ex.) |Company1(534553) Company2(3544)|
Right now this is how I'm parsing a line like this:

View 4 Replies View Related

VBA Coding To Detect Internet Connection

Nov 4, 2009

Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.

View 6 Replies View Related

Detect Hyperlinks And Source Links

Nov 21, 2013

I often have Excel sheets in which Excel tells me there are hyperlinks and content from other sources. I guess it happens because I copy something from one sheet to another.

First, is it possible to copy-and-paste from a sheet to another and only copy the concrete formulas and formats, and no source to the old file?

Secondly, how can I detect where these hyperlinks and links to other sources are? I see they exists, but I can't seem to find them. Does Excel have such function? Can I detect them with a VBA script?

View 1 Replies View Related

Detect Characters? Change Combobox?

Jun 5, 2008

let's say combobox1 has a list of:
apple (KG)
apple (PKT)
apple (BAG)
orange (KG)
Orange (PKT)
Orange (BAG)

and a command button.

Can I make it in a way that when command button detects (kg) in combox1, the caption of commandButton will change to "KG"? if detect (BAG) combobox1, then commandButton is "Bag"?

View 9 Replies View Related

Macro To Detect Cell Colors

Sep 8, 2009

We have a workbook with over 20 worksheets, each sheet with one or more tables in it. Several people use the workbook and we have decided that when someone changes a cell, they should mark the changed cell by filling it with blue using Format Cell. We have then nominated one person at end of each week who will check all the changes made during the week and either approve or reject them. So her task is to find all the blue cells, check them and then take the shading off.

What I would like to do is to create a macro to help her. I would like the macro to automatically identify all the blue cells in the workbook and give her a list of these cells. I wonder if someone could give me direction on a couple of things:

1) The command in the macro that would return whether a cell is shaded blue

2) The way in which I could cycle through all the content of each worksheet. I'm thinking worse case I would have to write instructions to go to each sheet, and then do an outer loop to go through each row, and an inner loop for each column (and the number of active cells varies by worksheet) -

View 9 Replies View Related

Detect Method Of Opening Workbook

Nov 1, 2009

Is there a way to tell (using VBA in the workbook open event) whether a user has opened a particular file by double clicking on the file icon or by using the Open Command in Excel ?

View 9 Replies View Related

Detect If Specific Workbook Open

Aug 17, 2008

Problem: I want a macro to detect if a workbook is open so I can skip the workbook and move onto the next item. I do not want the user to be prompted in any way. I do not want the workbook to be shared. I simply want to pass over the workbook (but be aware I have passed it over).

--Long Version--
About forty team members have their own workbooks and at dayís end the team leader uses a macro to poll each team memberís workbook in turn to gather work results. When gathering these results the macro also needs to write some admin data to each workbook.

I am aware of the workbook sharing option in Excel, but I am wary of using it. It is not a show stopper if a team memberís workbook is already open and work is not gathered on a particular day Ė weíll get it the next day. I just need to be able to trap the event and manage it. The central command I use to access a team memberís workbook is :

Workbooks.Open FileName:=WorkBookName................

View 2 Replies View Related

Detect Auto Refresh In VB And Run A Macro

May 22, 2006

My Excel spreadsheet is linked to an Access database to Get external Data. When the data is periodically refreshed ,while the spreadsheet is open, I need to automatically detect it so that a macro can be run each time to format the new data.
The Excel data is not a pivot table. The spreadsheet is for display of data only (no user updating).

View 5 Replies View Related

Detect If Text Or Number In Cell

Aug 11, 2006

I am try to do a test on cell to see if itís a string, and then do something if it is: IF Range ("a1").value "equal a string" THEN. But I am not very experience in VBA

View 2 Replies View Related

Detect Change Or Edit In Textbox

Jan 1, 2007

I need to detect when changes are made to a TextBox (Manual changes). In VB 'TextChanged' fulfills that function but there is no equivalent in VBA.

I also need to differentiate between adding a value to a TextBox which has a vbnull value (Does not need to trigger event) and editing or replacing the current TextBox value (Trigger an event).

View 6 Replies View Related

Detect If Cell Is In 'editing' Mode

Mar 13, 2007

I'm doing the vba programming for excel where I added a button to the excel, and when user click on the button, it will trigger my function.

My question is: if currently there is a cell been focused, how can I know whether that is in the 'editing mode' (ie, focus on that cell and double click on that) or merely 'focus' on that cell? Any of the excel object property for this?

View 9 Replies View Related

Detect If Cell Contains A Formula Or Constant

Dec 4, 2007

I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

View 9 Replies View Related

Detect/Return Computers IP Address

May 1, 2008

Is there a formula or VBA script available which, when executed, will find the IP address of the computer on which the code / formula is run (obviously - through Excel)?

View 9 Replies View Related

Excel 2003 :: How To Detect Pattern In Sheet

Aug 25, 2013

We have a huge excel-sheet, containing disputes with customers. Everytime there is a dispute, the customers data will be transferred into the sheet. The sheet is organized like this:

DATE / NAME / ID / COMMENT / FREQUENCY (how often one customer appears in the list) /ID2 (ID combination of the date + ID) /Weekday

And here comes the task:

Find all customers who appear 10 work-days(Mo-Fr) in a row. So if a customer appears Mo-Fr and keeps on being in the list the next Monday etc. it still counts as "in a row". Same with official holidays. If a customer appears at a certain date he is "booked" and it doesnt matter if a appears a second time on that specific date.

Attached to this post you find an example in which you can see the structure of my excel sheet!

example.xls

Note: I had to use a german version of excel when I made this little example - thats why the formula in the collum "Frequency" says "Zahlenwenn" its the german version of COUNTIF

Excel 2003

View 7 Replies View Related

Detect When Autofilter Drop-down Selection Made

Oct 6, 2009

Is it possible to detect when a auto-filter selection has been made ? Is that when an autofilter hides rows, the activecell could become hidden, which potentially may cause errant data entry, not knowing what cell is receiving entry as it is hidden from view.

if the worksheet contains a change event that autosizes the target row, it unhides the filtered hidden row in the process. I could say if target is hidden ignore autosize, but i would prefer to activate a visible cell (first visible cell for filtered column) on selection of autofilter.

View 3 Replies View Related

Detect Matching Values In Very Large Lists

Dec 9, 2009

I'm trying match values (and set a Yes / No result) from values in two very large lists.

List 1 (approx 170,000 rows) contains the 'Find What' values
List 2 (approx 980,000 rows) contains the 'In What' values

Values in list 1 will be unique in list 2, but not all values in list 1 will appear in list 2. The values in each list are all 16 character stings. This is a one-off otherwise I'd probably import into a AccessDB.

Using formulas is taking an absolute age to calculate using MATCH function, so I'm wondering if coding is the better angle.

View 3 Replies View Related

Can VBA Detect Whether Users Are On Outlook Or On Lotus Notes

Feb 9, 2012

I have a networked spreadsheet that users access, process data, and then e-mail the results to a common recipient.

Until recently we have all been on Lotus Notes - and my e-mail VBA worked OK.

However, some users are now using Outlook (the new Corporate standard) while some remain on Lotus Notes. The Lotus Notes users now say the e-mail part of the process no longer works for them.

Can VBA detect whether users are on Outlook or on Lotus Notes?

Should I give them a prompt - "Which e-mail do you use? Enter L for Lotus Notes or O for Outlook" - and if they answer L then use specific Lotus Notes VBA for the e-mail process or if they answer O use Outlook-specific VBA?

View 3 Replies View Related

Excel 2003 :: Automatically Detect 30-Day Readmission?

Dec 24, 2013

I'm designing a workbook to track psychiatric patient data. The workbook has identical worksheets for each month of the year (Jan, Feb, Mar, etc.), plus an Admin sheet for overhead. I need to devise a formula that will automatically detect 30-day readmissions (i.e., patients being admitted to the hospital within 30 days of a prior admission) and return a "Y" for a readmit and an "N" for a discharge or first-time admission. I have tried several variations on SUMPRODUCT with multiple conditions spanning multiple sheets (using INDIRECT and a monthly worksheet index -- A1:A12 -- on the Admin worksheet), but the results always turn out...wonky. (Sometimes a correct value is returned, and sometimes not. I can't figure out what is causing this inconsistency.) The relevant ranges from the monthly worksheets are:

B3:B52 : Date of evaluation
H3:H52 : Unique patient ID number
Y3:Y52 : Patient disposition (i.e., admitted or discharged)
AC3:AC52 : Readmission status (i.e., yes or no)

The formula should only return a "Y" value if a) the patient was previously admitted within 30 days of the evaluation date, and b) the patient was also admitted on the evaluation date.

I am using Excel 2003, so SUMIFS is not available.

View 9 Replies View Related

Auto Detect And Label The Peaks And Bottoms Of A Graph?

Jun 30, 2014

I was wondering if excel can auto label peaks and bottoms of a graph? Let me go further:

I have a graph that has one data number for every day of the year (365). This is a natural up and down curve, similar to a stock.

Can excel find the key high and low points? I have attached an example. In it you will see I have quickly labeled the key tops and bottoms. CHART TEST.xlsxCHART TEST.xlsx.

I basically omitted the peaks/bottoms that are very short term and irrelevant.

View 2 Replies View Related

How To Detect Leading Or Trailing Apostrophe And Quote Characters

Jun 23, 2014

I want to alert a user that he or she has typed in a text string that begins or ends with an apostrophe ' or begins or ends with a quotation mark " so that they can take corrective action in removing these characters. The reasons for this are superflous to this post but what I thought was a logical answer returns a formula error. Here's the scenario:-

A user enters a string of text in cell A1.

Cell B1 checks if the text, if any, in A1 begins or ends with a ' or a " and if it does, returns an "Error" message

The formula I've tried in B1 is =IF(OR(LEFT(A1,1)="'",LEFT(A1,1)=""",RIGHT(A1,1)="'",RIGHT(A1,1)=""")),"Error","Ok") but Excel will have none of it.

View 9 Replies View Related

Sub To Detect Any Change In Cell Selection Within Any Open Workbook?

Dec 3, 2008

I know of

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

but I would like to expand this idea to detect any change in cell selection across all open workbooks.

View 6 Replies View Related

Detect Duplicates In User Form Text Boxes

Jun 13, 2009

I have a user form with 4 text boxes and a command button. I have no problem looping through the textboxes to retrieve the value, but what is the best way to detect if any duplicates exist between the four text boxes? For example, in the command button's
If textbox one's value is "test" and textbox four's value is also "test", it should throw an exception. If a question like this has already been answered, a link to that post would surely suffice.

View 2 Replies View Related

Disabling Auto Format Detect Option Of Excel?

Mar 8, 2013

When i enter a value in an excel sheet cell ,Excel auto detect the data type and format that cell to that data type.

For example if i enter 4/4001, excel read it as date and convert the cell format from general to date and displays 4-Aprl.

However if i precede with " ' " this problem is solved but my question ios that can i permanently disable this auto detect option?

View 4 Replies View Related

ADVERTISEMENT