Dynamically Add A Hyperlink To A Shape In VBA

Dec 11, 2008

I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.

What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?

Here is how I create the shapes:


With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight)
.Fill.ForeColor.RGB = RGB(204, 255, 204)
.Line.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame.Characters.Text = StrRowName
.TextFrame.HorizontalAlignment = xlHAlignLeft
.TextFrame.Characters.Font.ColorIndex = xlAutomatic
.TextFrame.Characters.Font.FontStyle = "Bold"
.Locked = True
End With

View 9 Replies


ADVERTISEMENT

Which Hyperlink Was Clicked And Associated Shape Name

Feb 10, 2010

How do I capture which hyperlink has been clicked (selected in any fashion)?

Then get associated shape name.

View 11 Replies View Related

Hyperlink From A Shape To A Cell On Seperate Worksheets

Feb 10, 2010

I had getting a hyperlink from a Shape to a Cell on seperate worksheets and the macro works fine:

View 4 Replies View Related

Add Hyperlink To Shape That Refers To Website Contained In External Workbook Cell

Apr 13, 2013

I've been trying without success to hyperlink a shape in one workbook that refers to a website contained in a second workbook cell.

The hyperlinked shape is in the current workbook and the website address is in a workbook called "data source.xlsx", sheet 1, cell A1.

My last attempt was this - ('C:UsersLouiseDocumentsMA[data source.xlsx]Sheet1'!A1)

View 3 Replies View Related

Change Shape Text Without Selecting Shape

Mar 4, 2009

when i run the below code i get an error 438 'object doesnt support this property or method'

View 2 Replies View Related

Locate A Shape Within A Shape

Jan 24, 2014

On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"

I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.

In the real spreadsheet i will probably have around 10 different Areas.

Move Two Objects - Select Case.xlsm‎

View 5 Replies View Related

Hyperlink In Cell Have Email CODE But Hyperlink Is Not Clickable

Jul 2, 2009

I 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

View 10 Replies View Related

Hyperlink - Insert New Rows Without Losing Hyperlink?

Jan 30, 2014

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 Related

Hyperlink Address From Hyperlink Formula/Function

Oct 17, 2006

I 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.

View 6 Replies View Related

If Formula: =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good Shape","Bad Shape"))

Jun 23, 2009

I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.

View 4 Replies View Related

IF On Shape

Aug 24, 2009

code doesn't work

Sub test()

If ActiveSheet.Shapes.Name = ("five") Then
Range("B4").Select
Else
Exit Sub

End If

End Sub

View 9 Replies View Related

Change Shape Name

Feb 2, 2010

I have a shape in Excel called Canada. I grouped it with another shape and want this new grouping to be called Canada. But when I enter Canada in the shape name entry box, it doesn't actually change it (as it thinks I am referring to the existing Canada).

I need something like Names manager, but for shapes, not ranges.

View 9 Replies View Related

Shape A Textbox

Apr 1, 2008

Is it possible to find out if the selected item is a textbox through VB?

View 12 Replies View Related

Get The Name Of The Shape Clicked

Jan 21, 2009

Is there any way that I can get the name of the shape i just clicked to call a procedure?

I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).

I want to make just one macro procedure and catch from wich shape the macro was called.

View 4 Replies View Related

VBA Keep Shape Static?

Mar 21, 2014

How do i Keep shape where it is no matter if i scroll up or down.

Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub

View 6 Replies View Related

Add Shape & Rotate

Jan 2, 2007

i am trying to draw a shape (square or rectangle) using vba textbox's i have managed to draw 2 of the 4 lines.

i am stuck on the ShapeRange.Flip msoFlipHorizontal codeing.

i have attached a example of what iam trying to do.

the coorinates from the last line enterd are saved to sheet1 for use with the next line entered ...

View 9 Replies View Related

Shape On A UserForm

Dec 29, 2007

I want to create a shape connecting them and place that shape on a userform. I can create the area without any problems on a worksheet using ActiveSheet.Shapes.BuildFreeform but I don't know how to get it onto my userform.

I have looked through previous posts on similar topics and seen responses that it's as easy as copying the shape from the worksheet and pasting it into an image or frame on the userform. I can copy the shape to the clipboard without any problem. However, when I try to paste it onto an image or frame in a userform I get a message saying that the object doesn't support the use of paste. I've tried using both Selection.Copy and Selection.CopyPicture xlScreen, xlBitmap thinking it might treat a bitmap differently. When I paste I'm trying both UserForm1.Frame1.Picture.Paste and UserForm1.Image1.Picture.Paste but neither work, even though I have blank images and frames with those names on my form.

View 5 Replies View Related

Dynamically Change Links?

May 21, 2014

I have a link

='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13

Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes

im looking at

2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13

where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?

View 3 Replies View Related

Dynamically Transpose Data

Jul 11, 2008

I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.

There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.

View 14 Replies View Related

Update Cells Dynamically

Dec 7, 2008

I have a cell range of L3:N3 on Sheet 1 and on Sheet 2 in A1 i have the value of N3 of Sheet 1.

Now, if I move L3:N3 to eg. R10:T10 I want the value A1 ( Sheet 2 ) to be updated to T10 ( Sheet 1 )

Right now, it wont update dynamically, value of A1 stays at N3...

View 14 Replies View Related

Want To Reference Another Cell Dynamically

Jul 13, 2009

I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5

But I Need the G to change... I basically need
='SHEETNAME'! + E29 + 5 to equal

='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.

The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.

View 3 Replies View Related

Referencing Tabs Dynamically

Aug 24, 2009

Is it possible to access other tabs on my spreedsheet dynamically? =COUNTIF(July!$F$4:$F$200,$H7) not dynamic

A1 = July. =COUNTIF(A1!$F$4:$F$200,$H7) dynamic. but how is this done in Excel?? Is it possible?

View 2 Replies View Related

Dynamically Referencing Tab Names?

Nov 4, 2013

I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...

How would I go about dynamically picking up the sheet name. and referring to it?

For example if I have a column with all the different sheet names in it:

Col AA:
31-10-13
01-11-13
04-11-13
etc!

View 11 Replies View Related

Dynamically Set Print Area?

Feb 12, 2014

I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.

Is it possible to execute the code below when the user selects print rather than when opening the tab?

[Code] ......

View 4 Replies View Related

Add Numbers Dynamically Down A Column

Apr 20, 2009

In the attached sample workbook I am trying to add the vendor number down the column H for each PO. I have copied a few of the numbers into the column, (in red), to show the desired result.

Basically, I will have a report daily with a variable number of suppliers, and variable PO's for each supplier. I need to add the vendor # to each row.

View 3 Replies View Related

Grouping Of Rows Dynamically

Oct 21, 2009

I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.

I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.

View 6 Replies View Related

Dynamically Merge Cells

Nov 19, 2009

I'm am trying something that is still a little over my head...

Normally, I merge cells the easy way:

View 8 Replies View Related

Dynamically Variable Ranges

Dec 31, 2006

In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?

Here is a sample cell formula:

=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))

The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.

GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.

$A$2:$A$332 is the column which contains the G/L account number to be matched.

$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.

$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.

I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.

View 12 Replies View Related

Delete Last X Rows Dynamically

Dec 17, 2012

Is it possible to run a macro that will delete the LAST X rows? It must be dynamic, and now be fixed to a specific row.

View 8 Replies View Related

How To Dynamically Format A Number

Jan 2, 2013

I am trying to format a cell that contains the weekday number to display the name of the day but still keep its weekday number value. For Example, a cell contain the value of 2. This value is Monday as per Excel Weekday() value. I want to still keep the value as 2 but I want it displayed as Mon. If the value is 5 then I want it displayed as Thr.

Is there a way when creating a custom format display for a number that I can state 1="Sun"; 2="Mon"; 3="Tue"; 4="Wed"; 5="Thr"; 6="Fri"; 7="Sat".

View 7 Replies View Related







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