automatically link command button to cell value

Question

The code I am trying to make consists of 2 parts. The first part I managed to make, but I am having a hard time figuring out the second part.

First part: in a textbox you fill in a project number (e.g. 211), a command button then runs a macro and fills this number in a new row, generates a worksheet named 211 (within the workbook) and adds a new command button next to the project number.

So the idea is that on a main sheet there is a whole list with project numbers and next to each number there is a command button that goes to the corresponding worksheet. So there could easily be 50 project numbers, 50 command buttons and 50 worksheets. This is the code:

Dim MySheetName As String
Set pasteSheet = Worksheets("Mainsheet")
Dim aRange As Range

Dim sValue As Variant, findMe As Range, sWhere As Range
sValue = TextBox1.Value
Set sWhere = Range("B4:B700")
Set findMe = sWhere.Find(What:=sValue, After:=sWhere(1))

If findMe Is Nothing Then

LastRow = pasteSheet.Cells(pasteSheet.Rows.Count, "B").End(xlUp).Row
    Worksheets("Mainsheet").Range("B" & LastRow + 1).Value = TextBox1.Value

With Worksheets("Mainsheet")
    .CommandButton1.Copy
    .Range("B" & LastRow + 1).Offset(0, 3).Select
    .Paste
End With

Unload Me

Sheets("Copysheet").Copy After:=Sheets("Copysheet")
    Range("C3") = TextBox1.Value
    ActiveSheet.Name = TextBox1.Value

Else
    Unload Me
    MsgBox "Number already exists"
End If

Worksheets("Mainsheet").Activate
Range("A1").Value = 1

Second part: This part is a riddle for me. What I want to do now is, that every generated command button includes a code that automatically switches to the correspondig worksheet.

E.g. if I make a new project number named 214 (automatically filled in cell B5), the command button in cell C5 searches in cell B5 and goes to that worksheet. If i make another project named 215 (in cell B6) the command button in C6 searches the value in B6 and so on and so on...

Any help is appreciated, thanks.

Take a look at this : http://stackoverflow.com/a/9038809/4628637 You'll need to create a class to be able to generate controls with embedded code for events! — R3uK, Nov 17 '15 at 14:02
I have never worked with classes before, will take a look at it later, thank you. — M.Bergh, Nov 18 '15 at 07:38

brokencornets · Accepted Answer · 2015-11-18T12:21:15.033

1

Instead of using a button, a simple solution could be to put a hyperlink in column C instead.

With Worksheets("Mainsheet")
    '.CommandButton1.Copy
    '.Range("B" & LastRow + 1).Offset(0, 3).Select
    '.Paste
    .Range("B" & LastRow + 1).Offset(0, 3).Select
    .Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        projectNo & "!A1", TextToDisplay:="Link"
End with

One important point is that you'd also have to move the section where you create a new sheet to before this section to avoid an error when trying to create the hyperlink.

edited Nov 18 '15 at 12:21

answered Nov 17 '15 at 14:12

brokencornets

28
5

1

This one did the trick, it is another way but I like it. I did change a small part: the **projectNo** dirrectly by **Textbox1.value**. Thanks for your time, it did save a lot of time! – M.Bergh Nov 18 '15 at 07:41

automatically link command button to cell value

1 Answers1