Free Microsoft Excel 2013 Quick Reference

Change application to run on a hyperlink

I have on a notebook a cell with a hyperklink to a image of .jpg type. When
i click on hyperlink, excel (2003) run Internet Explorer 7 application, bat
i want to open and to run Picture Manager application as happen and run on
my desktop.
Thanks for your help
P.S. Scuse me for my incorrect exposure


Post your answer or comment

comments powered by Disqus
Hi there,

I have a list of published reports in a worksheet, some of which have hyperlinks to their location in Clarity (browser based project management system) and some of which have empty hyperlinks (with a given title)

I also have some code that generates and sends an email from lotus notes.

what I need to do is create some code that will allow users to click on a hyperlink titled "CPO Reports" in column C (there are many instances of the same empty link) and then a message box will appear with a yes/no button.

If they click yes then the email code will run.

any suggestions?
I can create command buttons on each line that will activate the code when they are clicked, but I want to do this with the existing hypelinks instead.

thank in advance,
Matt

I have two macros that i would like to schdule to run on a certain day each
week is this possible?

Thanks

I have a macro (see below) that begins by unprotecting the active worksheet,
runs several subs, then protects the sheet again. The user runs the macro
by clicking on a graphic in a locked cell. The sheet is normally password
protected. I'm pretty sure that something odd has happened - I'm sure the
user could run the macro without having to give the password to unprotect,
and now the macro won't run without they do that. Also, when the macro
finishes, the sheet is protected, but doesn't seem to have password
protection - Tools|Protection|Unprotect just unprotects it without asking
for the password. Maybe I'm going nuts... Anyway - is there a way of
building the passowrd protection into the macro, and also, is what happens
in the description above the norm? I was SURE the macro would run on a
password protected sheet without the user being asked for the password.

Macro:-
____________
Sub Main_MEMCARE()
ActiveSheet.Unprotect
Trim_Text
Color_Text
myRows
CC_OT
ALL_OT
ActiveSheet.Protect
End Sub
______________

All help and suggestions gratefully received,

TIA

Dan

--
Dan E
webbie(removethis)@preferredcountry.com

I'm trying to get a macro I've designed to run on any worksheet.

However, when I start the macro it errors and needs to be on a worksheet
that has the same name as the one I designed it on.

Is there any way to get the macro to run on a worksheet, regartdless of what
the name is?

I'm trying to get a macro I've designed to run on any worksheet.

However, when I start the macro it errors and needs to be on a worksheet
that has the same name as the one I designed it on.

Is there any way to get the macro to run on a worksheet, regartdless of what
the name is?

Hi,

I have a lot of small changes I make in a standardized form (an excel
spreadsheet). The forms are stored in multiple locations on the network but
I have a list in a worksheet that has hyperlinks to every form. I am trying
to find a way to speed the process up of making the changes. I put an
example of a small change below. Can I make a macro that would in a selected
range open hyperlinks from my list, make the change, save the doc and then
move down the list to the next hyperlink?

Thanks,

Todd

Sub changes()
' Keyboard Shortcut: Ctrl+q
'
Range("G15:G101").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Range("I90").Select
Selection.ClearContents
Range("G89").Select
ActiveCell.FormulaR1C1 = "1"
ActiveWindow.LargeScroll Down:=-2
Range("A1").Select
ActiveWorkbook.Save
End Sub

Hi guys. Below is a code that is triggered by an event, worksheet change. How would I slow this code down, to work every 60 seconds?

On Sheet1, Range A2 to I2, I have some data that changes a few times a second, and my code copies that data on the Sheet2 after it adds a row (so I can build a database of all data that comes in).

Now, there is way too much data coming in, so how can I only copy-paste the values (as I am doing right now) by at set intervals? For instance, every 60 seconds I would want my code to run?
Private Sub Worksheet_Calculate()

Sheets("Sheet2").Rows("1").EntireRow.Insert
Sheets("Sheet2").Range("A1:I1").Value = Sheets("Sheet1").Range("A2:I2").Value
End If
  
End Sub


Is there a way to change what is displayed on a hyperlink in vb code? I
would like for a input box to appear and ask what name would you like to
call the link?

Thanks

Greg

I am automating Excel by linking to batch files and log files on my computer
using the =hyperlinks() worksheet function. How can I stop the Microsoft
Office warning window that contains the message "Some files can contain
viruses ..." "Would you like to open this file?" < OK> <Cancel>; when I
click on a hyperlink?
"Application.DisplayAlerts = False" does not prevent this popup window from
appearing.

Hi,

I want to create a hyperlink to files on a shared workspace.

When I create the hypelink is looks similar to the following

"//SERVERNAME/shared folder name/folder a/folders b/folder c/filename.doc"

When I click 'ok' and try the hyperlink, it works ok.

However, if i close the file and reopen it, the hyperlink doesnt work.

This is because the hyperlink is changed (abbreviated) to

"../../../../filename.doc"

How do i stop this?

Or can someone tell me a way around the problem?

Thanks in advance

Hi Guys,

I have 3 macros done individually and now i am goin to put them into one master macro. I am using this piece of code to import 3 raw data files into 3 different sheets, this works fine but i don't know where to go from here. I want macro1 to sort sheet1's raw data then macro2 to sort sheet2's and macro3 to sort sheet3's data. How do i call them to run on a certain sheet.

Sub Open3_v2()
Dim i As Integer

For i = 1 To 3
Worksheets("Sheet" & i).UsedRange.EntireRow.Delete
sub
OpenFile i
Next
End Sub

Sub OpenFile(iCount As Integer)
Dim strFile As String
Dim wbkData As Workbook

strFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select file", "OK")
If Dir(strFile) = "" Then Exit Sub

Set wbkData = Workbooks.Open(strFile)

wbkData.Activate
ActiveSheet.UsedRange.Select
Selection.Copy

ThisWorkbook.Activate
Worksheets("Sheet" & iCount).Activate
Cells(ActiveSheet.UsedRange.Rows.Count + 0, 1).Select
ActiveSheet.Paste

wbkData.Close False

Set wbkData = Nothing
End Sub

Thanks,
Cathal.

I am looking to run a marco for a selection range, and the selection range could vary in size.
Using the Macro recorded (whilst turning on the relative reference) the Macro runs for a defined number of cells. I would like to run certain Macro, for different ranges of cells.

Chrisham

Hello all,

I've had a search and can't find a solution to my problem...

I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.

Any advice would be appreciated.

Thanks in advance,

Matt

Hi all
Is it possible to change visibility of Trendlines on a Scatter Graph?
i develop an app in .NET and need to make Trendlines visible or invisible by
user selection.
anybody have any idea to do this work?
Thanks a lot

My computer at work was recently switched from Office 2000 to Office 2003. I
noticed that after that happened I can no longer just hit "enter" on a cell
with a hyperlink to go directly to the linked page, instead I have to use my
mouse. Is there some sort of setting to get this back to being able to just
hit "enter"?

~Diana~

I am automating Excel by linking to batch files and log files on my computer
using the =hyperlink() worksheet function. How can I stop the Microsoft
Office warning window that contains the message "Some files can contain
viruses ..." "Would you like to open this file?" < OK> <Cancel>; when I
click on a hyperlink?

Hi guys..

I’m currently using a macro that I need to run on every worksheet in a workbook. The amount of worksheets in the workbook varies, however the last worksheet is always blank/available for use.

I’ve tried a few times to modify the macro so that it runs on every sheet in the book automatically with no success. Can anyone help?

Basically, I want it do the following:
1. Run on every worksheet in the workbook
2. Once complete on all worksheets, copy data in every worksheet and paste into the last worksheet in the workbook, or create a new named worksheet and paste into that if easier.

Here’s the macro I’m using at the moment.. any help would be great.

Public Sub OTC_TranTally_by_VA_Number()

' Variable Declarations
    
    Dim lastrow As Long, r As Long
    Dim Rw As Range
    Dim mySum As Long
    Dim lastrowA As Long

' Run 1st
' Delete's the Header Column from the spreadsheet

    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

' Run 2nd
' Delete's the columns that are not required for the calculation
    
    Range("A:A,B:B,C1,C:C,D:D,E:E,G:G,H:H").Select
    Range("H1").Activate
    ActiveWindow.SmallScroll ToRight:=5
    Range("A:A,B:B,C1,C:C,D:D,E:E,G:G,H:H,I:I,J:J,K:K,J:J,J:J").Select
    Range("J1").Activate
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A:A,B:B,C:C,D:D,E:E,G:G,H:H").Select
    Range("H1").Activate
    ActiveWindow.SmallScroll ToRight:=7
    Range("A:A,B:B,C:C,D:D,E:E,G:G,H:H,I:I,K:K,L:L,M:M,N:N,O:O").Select
    Range("O1").Activate
    ActiveWindow.SmallScroll ToRight:=5
    Range("A:A,B:B,C:C,D:D,E:E,G:G,H:H,I:I,K:K,L:L,M:M,N:N,O:O,P:P").Select
    Range("P1").Activate
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.LargeScroll ToRight:=-2
    Range("A4").Select

'Run 3rd
'Deletes the rows in the Item Count column with a zero value (debits)
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   lastrow = ActiveSheet.UsedRange.Rows.Count
   For r = lastrow To 1 Step -1
      If UCase(Cells(r, 4).Value) = "0" Then Rows(r).Delete
   Next r
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True

' Run 4th
' Deletes the entire row within the selection if _
  the ENTIRE row contains no data.

    Range("A1").Select
    If WorksheetFunction.CountA(Selection) = 0 Then
    Exit Sub
    End If
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False

        Selection.SpecialCells(xlCellTypeBlanks).Select

            For Each Rw In Selection.Rows
                If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                    Selection.EntireRow.Delete
                End If
            Next Rw

            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With

' Run 5th
' Sorts Column A into Ascending order so that the next calculation routine will work
    
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1").Select

' Run 6th
' Copies the text in Column D, then pastes as values for later calculation
    
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("E5").Select

  ' Run 7th
  ' adds the value of numbers in the first column
  ' together if the first to digits are the same.
  ' The results are then inserted into column E, in the row
  ' of the last matching number in Column A
    
   lastrowA = Cells(Rows.Count, "a").End(xlUp).Row
   
   oldKey = ""
   For r = 1 To lastrowA + 1
      newKey = Left(Cells(r, "a"), 2)
      If newKey <> oldKey Then
         If oldKey <> "" Then
            Cells(r - 1, "e") = mySum
            mySum = 0
         End If
         oldKey = newKey
      End If
      mySum = mySum + Cells(r, "d")
   Next

' Run 8th
' Delete's all rows with no value in Column E.
' Only returns VA numbers with a transaction

On Error Resume Next     ' In case there are no blanks
    Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    ActiveSheet.UsedRange

End Sub

Cheers.

My computer at work was recently switched from Office 2000 to Office 2003. I
noticed that after that happened I can no longer just hit "enter" on a cell
with a hyperlink to go directly to the linked page, instead I have to use my
mouse. Is there some sort of setting to get this back to being able to just
hit "enter"?

~Diana~

Hello all -

Is there a way I can attach a macro to a hyperlink? I'd like to be able to click on a hyperlink that will take me to another spreadsheet in the workbook, and then run a macro that fills in data on the form that I've been sent to.

Thanks much

Cjl

Hi, I'm sure what I'm about to ask is very simple but I just can't seem to find the answer anywhere. I have written a Macro that works in one cell but I can't seem to copy it so it works on all the other cells in a column. I am using the macro to hide multiple columns when a user selects a value from a validated list, the idea being that if they select a product, say 'Chest of Drawers', then they will only be able to see columns relevant to data you might apply to describe a chest of drawers. That works fine, but once they have applied data for the Chest of Drawers they will then need to do the same for lots of other furniture pieces. So they will then need to select the next piece of furniture on the next row and I want the macro to do the same thing and hide irrelevant columns and show relevant ones. I'm assuming I need to tell the macro to run on active cell but I need help. Can you help please? This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("ar1") Then
If Range("ar1").Value = "Wardrobe" Then
Columns("AT:BC").EntireColumn.Hidden = True
Columns("AS").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Dressing Table Stool" Then
Columns("AS:BA").EntireColumn.Hidden = True
Columns("BB").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Chest of Drawers" Then
Columns("AS").EntireColumn.Hidden = True
Columns("AU:BB").EntireColumn.Hidden = True
Columns("AT").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Bedside Table" Then
Columns("AS:AT").EntireColumn.Hidden = True
Columns("AV:BB").EntireColumn.Hidden = True
Columns("AU").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Dressing Table" Then
Columns("AS:AU").EntireColumn.Hidden = True
Columns("AW:BB").EntireColumn.Hidden = True
Columns("AV").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Underbed Storage" Then
Columns("AS:AV").EntireColumn.Hidden = True
Columns("AX:BB").EntireColumn.Hidden = True
Columns("AW").EntireColumn.Hidden = False

End If

End If

End Sub

Ok so here is what I am trying to do and i have looked all over the place to try to figure this out please help.

Ok so I have one master file now in this master looks something like this

A B C
1
2 (Location of Files) File Name

I want to set it up something like this:

1)Excel opens on its own at 7 am
2)Excel opens this master workbook
3)Excel runs a macro in this workbook which I need to do the following:

-Go to the location in A2 open the file with the same name as listed in C2
-Once the file in C2 is open run all the macros in that file then save the file and close that workbook then go to the next file in C3 and do the same thing. I would like this to run on the last day of the month and the first 5 days of the following month

I know this seems like a lot but any help would be great please and thank you!

I have this code in workbook open:


	VB:
	
 Workbook_Open() 
    Application.OnTime TimeValue("15:13:00"), "MyMacro" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and this in my macro:


	VB:
	
 MyMacro() 
     
     
    Application.OnTime TimeValue("15:13:00"), "MyMacro" 
     'Rest of macro code
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I set this up so that it will only run on the first of every month?

Hi,

I need help in making a VBA to create a looping hyperlink drilldown for each cell in the inventory_general wksht.

The purpose of this sheet is to allow a user to click on a hyperlink which will then reference a macro to
autofilter the data in the bin_lot worksheet to display all rows for that particular item number.

Apparently, you can't select all the cells and insert a hyperlink because all the hyperlinks will reference the first cell value for autofiltering.

This is what I tried but it's not working obviously.

	VB:
	
 HyperlinkDrilldown() 
     
     
     '
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ 
    "Bin_Lot!A1", TextToDisplay:=target.value 
    Range("A2:A21").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated. Thanks.

Good Morning All

I have a macro which I need to run on the first opening of the workbook each day. I do not want it to run on subsequent openings during the same day.

Any ideas?

Many thanks

Johny


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