Hyperlink macro

[akiz009]

I don’t have a good knowledge in VBA scripting.
I want to create a workbook for my customers, there will a master sheet. The master sheet will contain the list of my customer with a unique serial number. There will be a “detailed sheet” for every customer. So that I can find a customer by serial number on Master Sheet and move to that customer’s details sheet by a hyperlink.

I have made a Macro button which creates “New Sheet” arranged by a “Sample Form” with asking sheet name with a pop-up window.
Now I need to add one more thing to that macro. I want that macro to put a Hyperlink automatically on my “master sheet” on Column “E” one by another while creating each New Sheet. The hyperlink name should be same as the newly created sheet’s name.


Thank you sir

 

[Alan_P]

Hi akiz009,

Can you please post your current code so that we can work the hyperlink into it.

Cheers,
Alan.

 

[akiz009]

Sir I have uploaded my project on dropbox. I am very afraid if i get banned for putting a link here.
https://dl.dropboxusercontent.com/u/99954187/Akiz Workbook.xlsm

When i click on "New Customer", It creates a new sheet according to "Sample Form" sheet. And it asks for a "Name" for the newly created sheet automatically with a POP-UP window. That's fine.

But when i create a new sheet, i want to hyperlink that new sheet automatically on “Main Sheet" Column "E" one by another downwards.

(Suppose if i click on "New Customer" Button, It should create a new sheet and ask for a name. Then i put name "Customer 3", then it should put a hyperlink on Main Sheet, Col. E11 with name "Customer 3".


Thank you sir....

 

[Alan_P]

Hi akiz009,

I don't think there is a problem posting workbooks as long as the answer is posted so other people can see...

Additions highlighted in red

Sub MakeNewSheet()


    Dim ShtName$
[COLOR=#ff0000]     Dim LastRw As Long[/COLOR]
    
    Sheets("SAMPLE FORM").Copy After:=Sheets(Sheets.Count)

    ShtName = InputBox("Enter Sheet Name")

    Sheets(Sheets.Count).Name = ShtName
    
    Sheets(ShtName).Visible = True

    [COLOR=#ff0000]LastRw = Sheets("Main Sheet").Range("E" & Rows.Count).End(xlUp).Row[/COLOR]
    
[COLOR=#ff0000]    Sheets("Main Sheet").Hyperlinks.Add Anchor:=Sheets("Main Sheet").Cells(LastRw + 1, "E"), Address:="", SubAddress:= _[/COLOR]
[COLOR=#ff0000]    ShtName & "!A1", TextToDisplay:=ShtName & "-->" & "Link"[/COLOR]
    
End Sub

Hope this helps,
Cheers,
Alan.

 

[akiz009]

Sir,,,
You are great great great great great.... I am the happiest person right now...
Thank you sir,, God bless you

I hope other members will get this post very helpful

 

[Alan_P]

No prob Happy I could help

 

[akiz009]

Sub Insert_Pic1()
Application.ScreenUpdating = False
ActiveSheet.Rows("1:7").EntireRow.Hidden = False
Set Rng = ActiveSheet.Range("A1:B7")
fName = Application.GetOpenFilename("Picture files (*.jpg;*.gif;*.bmp;*.tif), *.jpgs;*.gif;*.bmp;*.tif", , _
"Select picture to insert")
If fName = "False" Then Exit Sub
ActiveSheet.Pictures.Insert(fName).Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Width = 815
.Top = Rng.Top
.Left = Rng.Left
.Height = Rng.Height
.Width = Rng.Width
End With
Application.ScreenUpdating = True
End Sub

Sir, i use this code for Adding a new picture with "Insert Image" dialogue to a certain area.
But the problem is that all the photos uploaded this way, are not saved inside the workbook. If the location of the photo changes, The photo gets missing when i re_open the workbook.

If i can upload customers photo through Shape > Fill > Picture, The photo gets embed inside the sheet. So, there wont be any chance for the photos to be missing. And i wont have to care for picture storage location.

Thank You sir

 

[Andrew Poulsom]

To avoid the link to the file on disk you can use the AddPicture method of the Shapes object. Check it out in VBA Help.

 

[akiz009]

Can i have an example code, that will create a rectangle shape (A1:B6) and open an "Insert Picture" window for browsing a new picture to fill the shape.
I have been trying all day to understand the method "AddPicture" but i fail.
Thank you

 

[Andrew Poulsom]

Try:

Sub InsertPic()
    Dim Rng As Range
    Dim fName As Variant
    Application.ScreenUpdating = False
    With ActiveSheet
        .Rows("1:7").EntireRow.Hidden = False
        Set Rng = .Range("A1:B7")
        fName = Application.GetOpenFilename("Picture files (*.jpg;*.gif;*.bmp;*.tif), *.jpgs;*.gif;*.bmp;*.tif", , _
            "Select picture to insert")
        If fName = False Then Exit Sub
        .Shapes.AddPicture Filename:=fName, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height
    End With
    Application.ScreenUpdating = True
End Sub