I need to trigger a hyper link say "Modify" based on the "True" Value of a "IF" condition in that sheet.
We can place that "IF" condition Right next to the Hyperlink if needed. But how to do that.
the If condition is like
=if(A1=B23, Trigger Hyperlink,False)
The cells are dynamic and even the sheet is Dynamic.(they can be any cell and any sheet)
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
how return a true/false based on a cell containing a string (text) vs a value?
I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.
Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
i have a formula in a cell:
=IF(ISERROR(VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE))&IF(ISERROR(VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE))
The first half of the vlookup searches for data and returns a dollar amount if true The second half of the vlookup searches for another data set and returns a dollar amount if true...
Both amounts could be the same, so I cant use a formula based on numeric value
the conditional format must determine which vlookup is returning the result
in other words I'd like to conditionally format (grey fill) if the first vlookup returns data and conditionally format (blue fill) if the second vlookup returns data
Probably have to "use a formula to determine which cells to format"
I have a checkbox where the values are reflected as True/False in a seperate column, what i simply want to do is take the value from one cell, and add 10% of the value each time a value becomes True.
For example in this value column after the check box has been filled out it might look like this:
False
True
True
True
False
False
False
The cell then needs to take the figure, lets say, 100, add 10%, then add 10% to 110, then again and so on for however many "True" statements are in the column.
I have tried with no success with various SUM/SUMIF/COUNT/COUNTIF/IF etc cant seem to get it to work, im not sure the cell refreshes correctly after the checkbox is filled out and its not registering the new "True" value as by default its all set to "False".
Basically I have a folder with file names in the format:
"XXXXX characterstring"
where X is a digit
E.g.:
45627 -jdsuuuds.pdf
93823 Coates.pdf
98221 dagggiisa d sass.pdf
And I have an excel sheet with a list of the numbers at the start of these file names, eg:
45627
93823
98221
So what I want to do is have each cell link to the file with the corresponding number. So I click "45627" and it links to "C:path45627 -jdsuuuds.pdf"
I could do it if the file names matched exactly to the cell value, but when they don't.
It's super easy when the file name matches exactly:
VB: ActiveCell.Hyperlinks.Add ActiveCell, "C:path" & ActiveCell & ".pdf"
VB:
Sub makelinks() ChDrive "c"
ChDir "c:pathl"
For Each ce In Selection
filee = Dir(ce.Value & "*.pdf")
[Code]....
im trying to get a cell to highlight a hyperlink based on the following formula
if(isnumber(search("t50", d7)), "correct", "this is where i want hyperlink to display")
basically im taking input from a cell entered by user ie if t50 is entered then its correct thats displayed, else i want to enter a hyperlink!
VB:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Work_Log!" & Sheets("Work_Log").Cells(b, 1), TextToDisplay:="View"
I want this to link to Work_Log!.Cells(b,1), however instead of grabbing the specific cell, it is pulling the contents of the cell and creating a URL of that. For instance if b = 1 and cell A1 contains the value "yellow" then this code is giving me the hyperlink to WorkLog!Yellow when I want it to read WorkLog!A1
I have created an employee schedule with 26 pay periods. Each pay period has its' own worksheet for scheduling employees.
To navigate to the worksheets I created a home page on one of the worksheets and would like to create an AutoShape for navigating with a hyperlink based on the pay period which is selected in cell B3. When the autoshape is clicked the user would go to the correct pay period worksheet.
For example Home Page:
Cell B3 contains drop down list of the 26 pay periods which the user selects the proper pay period (ie. 8Jun14-21Jun14)
Cell E3 is a cell reference on the sheet A1
Cell B6 contains hyperlink formula =HYPERLINK("#"&"'" & B3 & "'!" & E3)
Autoshape has formula =B6
When I click on the autoshape the hyperlink does not work.
I have a spreadsheet where when cell is clicked, it opens the hyperlink dialogue and allows a user to insert a hyperlink into the cell next to it.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 27 And Target.Row > 7 And Target.Row < 401 Then
If Application.Dialogs(xlDialogInsertHyperlink).Show Then
Target.Cut Target.Offset(, 1)
End If
End If
End Sub
So if the cell AA8 is click it allows the user to insert a hyperlink and the hyperlink is pasted in to the cell AB8.
What I need: Is for the cell AA8 to say "Click to Add Hyperlink" and then once a hyperlink is added AB8 to say in the cell "Hyperlink to Folder".
I want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.
View 3 Replies View RelatedI am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
View 2 Replies View RelatedI have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
how to trigger a message box?
if i type TP123 in cell A1 i want it to trigger a message box with a comment
I am preparing a list of calibration items that require yearly calibrated, how can I set the date and prompt me example 1 month ahead when the item is going to due soon.
View 3 Replies View RelatedI have attached a sample work book.
What I want to achive is the colouring of the cells in columns A-L using the trigger of the "Y" character in colums J-L
So when a Y is put in column J the cells to the left and including column J change to green. Then when a Y is put into column K the cells to the left and including K turn yellow. Finally when a Y is entered in column L the cells to the left and including L turn the lovely shade of Pink. It is possible the process will go from a Y in column J to a Y in column L mising out column K but I don't suppose this will matter.
I used to have it working in office 2003 to a fashion but have not yet got my head around 2010
I have a chart with 2 Y axis. I am attempting to write some code that will update both axis with the same max & min value that is triggered by the combobox selection. The code will update the axis but is not triggered by the combobox selection.
[Code] ...........
I want to trigger a macro that refreshes a pivot table but I only want to trigger the macro after 15 seconds. The reason is that I am pulling the source data from access mdb so I want only to refresh the data once the data is pulled.
View 3 Replies View RelatedHow do I amend this formula to have it only trigger on a certain month.
=SUMIF(C:C,1,F:F)
Date Description CatCost feesTotal Balance
20/02/12 PayPal Credit 10$0.10 $0.00$0.10 $0.10
20/02/12 PayPal Credit 10$0.01 $0.00$0.01 $0.11
26/02/12 Payment 1$174.69 $4.49$170.20 $170.31
I need it to look at what month it is and then the category.
Is it possible to trigger a macro on a condition like this?
If date more than 1 week arrange date in sequence order.
I am having trouble trying to get an MS Access Query to run from MS Excel automatically.
I am trying to create an automated trigger in MS Excel that will automatically run my Access query by the times I specify in my statement.
For example: I need to run a query in Access at 9am, 12 pm and 10pm, that's it, but I believe I need to do it via Excel, I don't want the data returned to Excel, I just want Excel to execute the query at those specific times!
A1 is a drop-down list, created from Data> Validation>List, which lists 3 different words (Text1, Text2, Text3). I have recorded 3 macros (Macro1, Macro2, Macro3). Here's what I want to happen:
When Text1 is selected in A1, I want Macro1 to run (same for Text2/Macro2 and Text3/Macro3). I want the user to be able to change this value as many times as they wish and have the corresponding Macro run each time. I've tried creating the appropriate code in Editor using other threads on this forum, but I can't seem to figure it out.
I am encountering a strange situation with my Excel 2000. I have a public function, in a module in the VBA project associated with my workbook. But I'm not calling it from nowhere inside the code, or from other macros - it is not being referred anywhere in the workbook. Yet, after I make a slight change in code and not save my changes, when I return to the workbook and select a value from any cell with a validation-list (regardless of the sheet where it resides), that particular function is being executed!
View 9 Replies View RelatedI have already use excel web query, and set every 5 minutes auto update web.
And here is question , I want to use vba event to trigger when cell's value changed.
Unfortunately,
Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
This code didn't trigger successfully,I think that web query make cells
value changed didn't trigger the event.
Is it possible, that while running code that the code can say initiate the click event on a command button on another sheet.
Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"
Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?
I have a table which says that this is the amount of coloured cells we have in another sheet.
For e.g.
Field Name Code A
Item Description 5
Now, the item description column has 5 cells in another sheet which are filled in with "Yellow" Colour. So what i want to do is to click on this 5 in sheet 2 in this case as per the attached sample which takes me to the filtered result on sheet1 of 5 yellow coloured cells under the column of Item description including an additional filter of Code "A"
