Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Hyperlink Button To Another Worksheet


I have an existing button which has either a macro or hyperlink assigned to it which links to another worksheet within the workbook. I would like to change the worksheet it is linked to. I have tried right click - edit hyperlink and picked a different sheet, and also assign macro - record new macro. I have even removed hyperlink, then inserted new but no matter what it still goes back to the same sheet.

I am in this position after copying and pasting an existing button. I'm not sure how they designed it from scratch so I would really prefer to just change the link.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Command Button Hyperlink
If I insert a command button into an excel spreadsheet on 'Sheet1', how do I make it a hyperlink button that will take me to cell A1 on the 'Sheet2' tab?

View Replies!   View Related
Use A Command Button To Activate A Hyperlink
I am trying to write a macro that activates the following funtion in cell D1.

=HYPERLINK("#"&C2,"")

I have recorded the following macro but it only jumps to the cell the function is in rather than actually activating the function and jumping to reference which has been selected in C2.

View Replies!   View Related
Hyperlink To Worksheet
how to create a Hyperlink to a specific worksheet in a workbook instead of just the workbook?

View Replies!   View Related
Worksheet Change Hyperlink
I have a question regarding hyperlinks...now what i wish to do is force a hyperlink in what i thoguht would be esily dne in a worksheet change event...hw wrong was i.

Lets say i type 'test' in A1 and press enter...now because i ave pressed enter A1 is no longer the active cell so the code in the sheet change event inserts the code into A2 but because A is blank the 'Text to display' becomes the hyperlinks address.

I wrote a simple with/ end with thinking it would work but it doesnt because of the issue above (Hope i am making sense here)

I got round it by copying the activecell (which would be the cell underneath) and pasting it back to the original target cell, which because it contains text does what i wantbut i am hoping for a much cleaner solution.....

My target column is '1' and only one i will just need to exit the sub anywhere else on the sheet

View Replies!   View Related
Vba Hyperlink To Local Worksheet
I would like to, using VBA, assign a hyperlink to an excel cell that links to another worksheet within the same workbook. I've tried the forums, and combined with a previous post and MS Excel Help File, I've come up with the following attempt:

With Worksheets("Test Destination")
.Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _
TextToDisplay:=(#1/1/2007# + counter)

End With

strHyper2 is previously defined: strHyper2 = "[WeatherTester.xls]1!A1"

My spreadsheet file is "WeatherTester.xls", my worksheet is "1", and "A1" is the desired link location for the cursor. Counter is an integer. I get the error "Invalid procedure call or argument". I also tried to enter a hyperlink directly through the formula property, but Excel didn't like the single quotations I had to use within the HYPERLINK function. The hyperlink formula works in the following form: =HYPERLINK("[WeatherTester.xls]1!A1","1 Jan")

View Replies!   View Related
Hyperlink To Show Up On A Different Worksheet In The Same Workbook
Im trying to get a hyperlink to show up on a different worksheet in the same workbook.

in the orginal cell (in this example L11) i have placed a hyperlink to an external file.

in the cell on the other sheet i have placed

=Sheet1!L11

this shows the text up (in this example : test ) but the link has disappeared.

how i can get the link to show up aswel as the text?

View Replies!   View Related
Hyperlink To Flow To Detailed Worksheet
Is it at all possible to have a hyperlink that when clicked will take the user to detailed information on another page? I have a summary information with account numbers on one page and would like to click on a hyerlink related to the account number and take the user to the detailed information on another page.

View Replies!   View Related
HYPERLINK Function To A Second Worksheet In One Workbook
I'm having a problem with the HYPERLINK function. I am trying to make jumps from an index sheet to the large worksheet with all the actual info in it. The name of the file is SKU and the large info sheet is also called SKU. First I took the SKU sheet and numbered the rows (="A"&CELL("row",A1)), then in the index worksheet I created a VLOOKUP that finds the corresponding chapter title and returns that value.

Worksheet 1

A B C
1
2 100 =VLOOKUP(A2,SKU!$A$1:$B$3,2,FALSE) ="[SKU]SKU!"&VLOOKUP(A2,SKU!$A$1:$B$3,2,FALSE)
3 150 =VLOOKUP(A3,SKU!$A$1:$B$3,2,FALSE) ="[SKU]SKU!"&VLOOKUP(A3,SKU!$A$1:$B$3,2,FALSE)

I used the HYPERLINK formula in column D: "=HYPERLINK(C2,A2)" and it returns something that looks like the right thing but the link won't open.

View Replies!   View Related
Hyperlink To Specific Data In Another Worksheet
I would like to link from worksheet A to worksheet B using the following scenario:

Worksheet A has the following cells, each of which is linked:

a
b
c
d

Worksheet B has the following cells which worksheet A is trying to link to:

a
b
c
d

The problem arises when I sort or add rows to worksheet B:

b
a
d
c

I want "a" in worksheet A to always find "a" in worksheet B, no matter where it is. The columns will not change but it will move to different rows.

View Replies!   View Related
Import Folder Structure Into Worksheet & Hyperlink
I have found the code shown below that imports Folder Contents into a Worksheet in Excel, however i need to import a folder tree structure for a set of folders into a worksheet not the folder contents. Is it possible to do this?

Could the code i found below be altered to list the folders not the folder contents?

The code is:

Sub Print_Dir_Contents()
Dim Input_Dir, Print_File As String
Input_Dir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & Chr(13) & Chr(13) & _
"for example:C:My Documents*.*")
If Input_Dir = "" Then Exit Sub
' If you want only to print a specific file type, you can
' substitute the "*.*" with "*.xl*"
' (for Excel files only) for the directory specified in the
' InputBox above.
If Application.OperatingSystem Like "*Win*" Then
Print_File = Dir(Input_Dir) & "*.*"
End If

View Replies!   View Related
Run A Button From Another Button From A Different Worksheet
i'm using excel 2003 SP2. I have several sheets with the same buttons. And i have a final sheet where I collect a summary from every sheet. I want to create in my summary sheet a button that runs all the buttons from the other sheets, so i can get all the info.

View Replies!   View Related
Put Hyperlink Code In This Button Code
Sub Increment()
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row
If IsNumeric(Cells(Lr, "A")) Then
Cells(Lr + 1, "A").Value = Cells(Lr, "A").Value + 1
End If
End Sub

This adds 1 to the previous cell and displays in the next available cell.

How could I make it sao that when this number enters in the cell it hyperlinks automatically to the master file?

Before this button was introduced I used this:

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Err_App_WorkbookBeforeSave

Dim hl As Hyperlink

For Each hl In Me.Hyperlinks
If Wb.FullName = hl.Address Then
Application.EnableEvents = False
Cancel = True..............


View Replies!   View Related
Click The Master Button, That Would Click Another Button On A Different Worksheet
Running into this issue of "transworksheet macros"

I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.

What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.

I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.

View Replies!   View Related
Hyperlink In Cell Have Email CODE But Hyperlink Is Not Clickable
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 Replies!   View Related
Hyperlink Address From Hyperlink Formula/Function
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 Replies!   View Related
A Button To Switch To A Worksheet
I have made a little program for a customer at work which has 4 different worksheets.

Each worksheet works out a cost based on different prices.

I would like to make a 'Welcome' worksheet, with buttons on it so my customer can click the button which corresponds to the option he would like to cost, and then excel automatically selects that worksheet.

how to create buttons but don't know the code to but behind it. It would be nice to also hide the other 3 worksheets when they have not been selected by the welcome page.

View Replies!   View Related
Can I Add A Submit Button To Worksheet
I'm working on an order form in excel and wondered if someone can tell me if it's possible to add a 'submit button' to a worksheet (order) that would do the following,

1.) print 3 copies (orders) of the current sheet

2.) create a new worksheet in the same workbook with a new order number (a specific cell in the worksheet) one higher than the current one. Also naming the new worksheet with the new order number.

3.) Save the current workbook.

Maybe I'm getting a little ahead of myself here but any help would be much appreciated as I'm really rusty at this...

View Replies!   View Related
Create A Button In Worksheet
creating a button (using VBA code) in a excel worksheet that can initiate a range of output data in a col (say (C1:C100)) by activating the therein formula of each cell (same as what F2 does) and then entering the cell.

Actually I have to do manually each time for each output cell after opening the worksheet that I don't want.


View Replies!   View Related
Macro For Button On Worksheet
I am trying to make a button that puts the formula back into the cell after a manual entry

View Replies!   View Related
Set Worksheet Button Properties
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).

View Replies!   View Related
Disable Worksheet Button
I have buttons on each of my sheets, when one is clicked code is called in my module ("modAPI"), and from that code I wish to disable and enable certain buttons, and/or change the caption of those buttons. However when I try to do:

With ws
btnRefresh.Enabled = False
End With

I get Error 424, Object Required. I've tried recording a macro of what I wish to do, and it gives me ActiveSheet.Shapes("btnRefresh").Select, but this doesn't give me the option to enabled/disable or change the caption of the button.

View Replies!   View Related
Get Index Of A Button Added To Worksheet
I have created a calendar to enter various locations. At present they are set up using VBA and are called from buttons on the calendar. What I want to do is add a button to the worksheet and then edit its text. I will call a subroutine to use the edited text as the name I want in the calendar. What I cannot figure out is how to return the index of the button have just added so i can alter the button text. I appreciate I could cycle through all the shapes on the sheet but as I do not know the number of the one I have just added I cannot work on the correct one. I suppose I could cycle through all of them and use a carrier to count how many there are then add one to get the latest but that seems very crude not mention time consuming as I allready have 504 text boxes on the claendar.

View Replies!   View Related
Macro To Print A Worksheet With A Button
I am trying to create a simple macro to print a worksheet with a button. The worksheet will have additions/deletions on a daily basis, so it need to adjust accordingly. I cannot figure out how to do the loop macro.

View Replies!   View Related
Command Button To Save My Worksheet As A PDF
Not to sure if it can be done but i would like to have a button on my spread sheet that once pushed it automaticaly saves my worksheet as a PDF in a selected company folder in my documents.

I know if you go to file and save as PDF, then save in selected folder will work, just looking for a quicker way so my staff just needs to push one button on the worksheet and it does it all. (dummy proofing the form i guess)

View Replies!   View Related
Adding Sort Button To Worksheet
I have data in several (50+) columns. I want to be able to sort all my data by the column I select with one click. So, I basically want a button over each column that sorts the data by that column. It would be ideal to have the same "Sort" macro that is attached to all the buttons. The macro should somehow know that the column the button is in, is the column to sort by.

View Replies!   View Related
Controlling Button Properties On A Worksheet
I am trying to change the text that appears on a button on a worksheet via a macro. There is only one button on the worksheet so in the macro I use

MyCaption = activecell.value 'Pick up the button text from the spreadsheet
For each sh in activesheet.shapes
sh.caption = MyCaption
'I know this would do all, but only one button as I said
next sh

which generates "Object doesn't support this property or method".

This is strange because if you get the properties box up, Caption definitely shows as a property of a button control.

View Replies!   View Related
Button/Link On Worksheet To Sort
I would like to have a button or a link on my spreadsheet that when it is clicked will sort my table with a pre-defined sort.

View Replies!   View Related
Reference Worksheet Command Button
I need to enable and disable command buttons in my code. These command buttons are located on sheets. I assume that I can simply set the Enable property, but I am having trouble referencing the command button. I thought I could simply reference it by name but that is not working.

View Replies!   View Related
Adding & Deleting Button On Worksheet
I am trying to create two macros. The first macro will add a button, and the second macro will delete the button. I am noticing that when I add the button in the first macro, Excel is automatically naming the button (i.e. Button 26), and then when I try to delete the button with the second macro, this button name is not recognized. How can I name or set the button as a variable so that I can successfully run these two macros?

View Replies!   View Related
Option Button Name Changes When Copied To New Worksheet
I have two option buttons (Designerbutton1 & Designerbutton2), when I copy them and paste them to a different worksheet their names change to Optionbutton# (# = next available number). Anyone know why & if I can prevent this. I cannot have a code that says Optionbutton16 should be renamed to what I want it to, because right now 16 is the next available # but what if I add an Optionbutton16 later on my own.

View Replies!   View Related
Code For Button Errors Due To Worksheet Name Change
My code works well if the worksheet name is Sheet1. It will change constantly. I have alot of other similar workbooks that are not affected and cannot figure this out. Here is the code that I get the error at: ....

View Replies!   View Related
Hide Command Button If Worksheet Is Protected
I want to hide a command button when a worksheet is protected and unhide when the sheet is unprotected.

View Replies!   View Related
Determine Chosen Option Button On Worksheet
I am having trouble writing a macro that checks the value of an option button to change a string accordingly. The ActiveMonth variable is used to copy information across from one work sheet to another, with the worksheet depending on which of the option buttons is selected.

Static ActiveMonth As String

View Replies!   View Related
Determine Position Of Shape/Button On Worksheet
I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.

I have searched and read about this issue on several forums, and they all indicate that i need to use something like

application.caller.topleftcell.row

to get the row number and

application.caller.topleftcell.column

to get the column number.

However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.

i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.

Private Sub Workbook_Open()
Dim name
Dim time As Integer
time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")

View Replies!   View Related
Command Button Exist In Active Worksheet Or Not
I am using following command to check if a command button with 'Email This Page' written on it Exists in active worksheet or not. It always gives not present.

Dim s As String
On Error Resume Next
s = Application. CommandBars(1).Controls("Email This Page").Caption
If Err.Number = 0 Then
MsgBox "It exists"
Else
MsgBox "Not here"
End If

View Replies!   View Related
Activating Worksheet By Clicking A Command Button On Another
What command should I use if I want to activate sheet1 by clicking a coomandbutton1 on sheet2?

View Replies!   View Related
Show Command Button On Worksheet When Cell = True
On the attached example, i have a check-box with controls cell M4.

There is also a command button on here, which i want to be visible when the cell value of M4 is "TRUE" and not visible when the cell value is "FALSE".

I assume i need to write some code into the worksheet to do this but don't know what to do.

View Replies!   View Related
Find Active Cell On Another Worksheet Using Command Button
I want to use one button with a macro attached to it to locate data on a different worksheet. So I highlight the "activecell" I want to find on the other spreadsheet then click on the button and it goes to the correct spreadsheet and filters for the data I want (relevant to the activecell).

View Replies!   View Related
Create Button & Assign Macro When Worksheet Added
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?

how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.

View Replies!   View Related
Button Position/Coordinates On Worksheet. Discrepancy Between Versions
Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;

Set sbut = Sheets("Listings").Shapes.AddFormControl(xlButtonControl, _
Range("J" & count).Left, Range("J" & count).Top, 50, 12)

When I try to find the row of the clicked button using the following code;

Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate

The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;

Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If

Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.

View Replies!   View Related
VBA Cell Select Button To Jump Around To Standard Locations In Each Worksheet
I'm using a button in my sheet to jump around to standard locations in each worksheet. Generally in the active worksheet I have these three buttons working perfectly.

However I have one button that takes you from the worksheet into the dashboard. The problem is if you had scrolled the dashboard around you may be put at your last place. I want the screen to jump to and center on R1C1

View Replies!   View Related
Show/Hide Worksheet Command Button Based On Cell Value Being In Another Range
i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.

View Replies!   View Related
Reference Cell Based On Button Location & Determine Button Used
I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA.

Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
' Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LRange = "B" & CStr(LRow)
'Change date in column B, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Date
'Clear date in column B, if checkbox is unchecked
Else
ActiveSheet.Range(LRange).Value = Null
End If
End Sub

Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............

View Replies!   View Related
Macro Works With Form Button But Not Command Button
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"

ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit

View Replies!   View Related
Running The VBA That Is Assigned To Multiple Cmd Button On One Cmd Button
I have a workbook that contains 18 sheets, on 14 of the work sheets I have a comand button that runs the following
Private Sub CommandButton1_Click()
' Range("B3:B53").Select
' Selection.ClearContents

Dim nloop As Long

Range("C3:K53").Select
Selection.Copy

Range("B3:J53").Select
ActiveSheet.Paste

For nloop = 3 To 53

If nloop 3 And nloop 12 And nloop 15 And nloop 16 And nloop 23 And nloop 28 And nloop 41 And nloop 47 Then Range("K" & nloop).Value = 0

Next nloop

Range("B2").Value = DateAdd("d", 7, Range("B2").Value)
Range("L7").Select
End Sub
I want to put a comand button on sheet18 that will run the code so it does the same thing as pressing each command button on each sheet.

the sheets that I have the cmd on are:
Sheet1, Sheet2, Sheets3, Sheet4, Sheet5, Sheet6, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet15, Sheet16, Sheet17

View Replies!   View Related
Toolbar Command Button As Toggle Button
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.

.State = msobuttondown
.State = msobuttonup

Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.

View Replies!   View Related
Uncheck Check Box + Check A Option Button Using Command Button
When I press a command button I want to:

Uncheck a checkbox made with Control Toolbox

Check a particular Option Button within a group box made with the Forms Toolbar.

EDIT: Clarification, I want both things to happen when I click one button.

View Replies!   View Related
Use A Command Button Instead Of The F9 Button

Private Sub cmdCalculate_Click()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub
Is there a way to disable the F9 key and make the user press this Command Button instead? Right now this does not work.


View Replies!   View Related
Add Button If No Button Exists Already
Is that each time the user clicks the button on the first worksheet a button is added. And the buttons begin to multiply like bunnies. I have been trying to find a way to have the macro check to see if a button has already been created and not create another if one is there.

Sub Paste10Case()
Windows("Book1.xlsm").Activate
Selection.Copy
Windows("Book2.xlsm").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.Columns.AutoFit
ActiveSheet.Select
'If ActiveSheet.OLEObject.Name "Click here to print" Then
ActiveSheet.Buttons.Add(500, 1000, 75, 40).OnAction = "PageSetup10"
ActiveSheet.Shapes("Button 1").Select............................


View Replies!   View Related
Last Button And Add Button On Userform
I have created a userform for users to enter data and can not seem to make it work correctly.

I am having a issue on the Row Number Textbox. I am trying to get this textbox to reflect the current row number that I am viewing with the Userform.

I am also having a issue with the Last and Add New button.

I have formulas in Column A and Column H and when I try to go to the last Row or Add a new record it recognizes the formulas and it takes me to the row below where the formulas are. (Example Row 601 instead of Row 3)

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved