×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Dynamic sheet creation button in excel using VB?

Dynamic sheet creation button in excel using VB?

Dynamic sheet creation button in excel using VB?

(OP)
I havent used exel too much and even less VB, so my current code might be a bit messy as im building it bit by bit.

Anyways Im trying to make a button that creates a sheet, copies stuff from another sheet and pastes it in the new one, creates a button that takes user from the new sheet back to where the original creation button is and creates new row on that page and button that takes the user from that row to the created page.

Problem is that Im not really sure how to get the name of created sheet to the button on another sheet automatically. Below is the code and the last row is prolly where the problem is. This code doesnt yet have any dynamic position features or such and its just a test untill I get it working properly and then ill be able to use it on the actual excel workbook.

Private Sub CommandButton1_Click()
    Sheets("kopioitava").Select
    ActiveCell.Range("A1:E8").Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
    Selection.OnAction = "Painike2_Napsautettaessa"
    Dim nimi As String
    nimi = ActiveSheet.Name
    Sheets("toiminnot").Select
    ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
    Selection.OnAction = Sheets("nimi").Select
End Sub


That code has some finnish words, if u were wondering what they ment. So anyways the last row before End Sub is where the problem is. Its supposed to tell the new button that when its clicked it should take the user to sheet that has the freshly created sheets name. Could someone tell me how to do it right? Currently it doesnt work.

RE: Dynamic sheet creation button in excel using VB?

Hello,

Not sure whether this is what you want but it does work (I just don't know if this is what you want).

The code in the relevant sheet is as follows

Public Sub CommandButton1_Click()
    Sheets("Sheet1").Select
    Range("A1:E8").Copy
    'Selection.Copy
    Sheets.Add
    nimi = ActiveSheet.Name
    ActiveSheet.Paste
    ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
    Selection.OnAction = "GOTO_SHEET"
    Sheets("Sheet3").Select
    ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
    Selection.OnAction = "GOTO_SHEET"
End Sub

(this is just your code amended, you will need to change sheet names as appropriate).

In a new module, you need this code

Global nimi As String
Sub GOTO_SHEET()
Sheets(nimi).Select
End Sub


Does this do anything like your requirements?

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Dynamic sheet creation button in excel using VB?

(OP)
Thanks, that helped a bit. But it seems that i need something a bit different. I mean the code works fine and does what i wanted, but when i use it again (press button), it links the previous buttons action to the latest sheet and doesnt leave it to the one i created earlier.

So I guess i would need to know if its possible to make it so that everytime that buttons creates the new sheet and 2 buttons it makes the button that takes user to the new sheet have kind of a permanent effect.

Now when i press the button it doesnt what i needed, but when i press it again, it does what i want but the previous buttons are directed to the latest sheet too.

So what i would need is: press create button at page 1 -> create sheet(1)+button to page 1+button to sheet(1)
press create button at page 1 again-> create sheet(2)+ button to page 1+ button to sheet(2)

I hope thats not too poorly explained. Current code is this:

Public Sub CommandButton1_Click()
    Sheets("kopioitava").Select
    Range("A1:E8").Copy
    Selection.Copy
    Sheets.Add
    nimi = ActiveSheet.Name
    ActiveSheet.Paste
    ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
    Selection.OnAction = "toimintoihin"
    Sheets("toiminnot").Select
    ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
    Selection.OnAction = "GOTO_SHEET"
End Sub

and on module:

Global nimi As String
Sub GOTO_SHEET()
Sheets(nimi).Select
End Sub

Private Sub toimintoihin()
Sheets("toiminnot").Select
End Sub


So that works fine, but i realized that what i need might be something that creates new macro for each button, so that the target of the button wont change every time i press the create button.

So if someone could help me with this a bit more I would be really grateful :) Thank you in advance.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources