+ Reply to Thread
Results 1 to 8 of 8

Code for using prompt boxes within a sheet?

Hybrid View

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    46

    Code for using prompt boxes within a sheet?

    Hi, can anyone help with the below?

    I have a tab called “Template”. What I would like to do is to add a button to my UserForm that does the following;

    Copies the Template tab and inserts it before the 20th sheet.
    Then have a prompt box to appear and ask me to rename the sheet
    Then for another prompt box to appear and ask me for a title that will be inserted into cell A1.

    Private Sub CommandButton5_Click()
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
    What code do I need to be able to get the prompt boxes to appear and make the changes that I require?

    Thanks,
    Last edited by Bandicoot; 12-19-2012 at 05:21 AM.

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Code for using prompt boxes within a sheet?

    Sub MoveSheet()
    
        Dim MySheetName As String
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
        Sheets(MySheetName).Select
    End Sub
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Code for using prompt boxes within a sheet?

    Sorry, forgot the last part of your question

    Sub MoveSheet()
    
        Dim MySheetName As String
        Dim MyCell As Variant
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
        Sheets(MySheetName).Select
        MyCell = InputBox("Please enter value to be placed in cell A1: ")
        Sheets(MySheetName).Range("A1").Value = MyCell
    End Sub

  4. #4
    Registered User
    Join Date
    11-29-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Code for using prompt boxes within a sheet?

    Hi Sean,

    That is great - thank you.

    Just a quick question, after using your code, I think I'd rather have cell A1 to be the same as the name of the sheet that I specify in the first step.

    Sub MoveSheet()
    
        Dim MySheetName As String
        Dim MyCell As Variant
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
    
    End Sub
    Do you know how the above will need to be modified so that after I re-name the sheet, that same name is copied into cell A1?

    Also, I'm just curious, but if cell A1 on the template has the following text in it "Name", can the code be written so that whatever I enter in the prompt box is just added onto the end of the the text in A1?

    Thanks,

  5. #5
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Code for using prompt boxes within a sheet?

    try this

    Sub MoveSheet()
    
        Dim MySheetName As String
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
        Sheets(MySheetName).Select
        Sheets(MySheetName).Range("A1").Value = "Name " & MySheetName
    End Sub

  6. #6
    Registered User
    Join Date
    11-29-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Code for using prompt boxes within a sheet?

    Sorry, my previous post didn't explain what I was after very well.

        Dim MySheetName As String
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
        Sheets(MySheetName).Select
        Sheets(MySheetName).Range("A1").Value = MySheetName
        MySheetName = InputBox("Please enter Date: ")
        Sheets(MySheetName).Select
        Sheets(MySheetName).Range("A2").Value = "Beginning from " & MySheetName
    End Sub
    After entering the name of the tab and having that displayed also in cell A1. I would like another prompt box that asks for a date and then places that value in cell A2 along with the text "Beginning from". I've tried to describe it in the code above, but I know it's not correct. How do I distinguish between the two prompt boxes?

    Thank you.

  7. #7
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Code for using prompt boxes within a sheet?

    Bandicoot,
    You have to assign a new name to the string/value being returned from the inputbox.
    You also have to declare the dimension of that name ie:String or date etc.
    Because you are adding the date to a string, it has to be a string. If you were adding the date to a seperate cell you could declare it as a date.
    Sub MoveSheet()
        Dim MySheetName, MyDate As String
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(20)
        MySheetName = InputBox("Please enter sheet name: ")
        Sheets("Template (2)").Name = MySheetName
        Sheets(MySheetName).Select
        Sheets(MySheetName).Range("A1").Value = MySheetName
        MyDate = InputBox("Please enter Date: ")
        Sheets(MySheetName).Range("A2").Value = "Beginning from " & MyDate
    End Sub

  8. #8
    Registered User
    Join Date
    11-29-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Code for using prompt boxes within a sheet?

    Sean,

    Thank you for all of your help. Greatly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1