0

I am working on updating my code and have the bulk of my code figured out, but need help to eliminate one step and move the calculation inside the macro. My old macro was dependent on an InputBox for the "Name" variable, but that is subject to user error, etc.

Updated dream and aspiration: In a regular excel file, the user is able to select a date from the date picker and the worksheet calculates the Year and Pay Period number. I would like that to be done inside the macro so it's completely hidden to the user. My current code is below, along with a sample file.

Currently, the following works, but is open to user errors (input, manual calculation, etc.). I would like to replace the lines marked with -*-*-*

Private Sub DupSheet(control As IRibbonControl) 
 
Dim Name As String 

    Name = InputBox("Enter the name for the new sheet.") 
    If Len(Name) = 0 Then 'Checking if Length of name is 0 characters 
    MsgBox "Please enter a valid name!", vbCritical 
    Else 
    Application.ScreenUpdating = False 
    On Error Resume Next 
    'The below code is good...
    ActiveWorkbook.Sheets("Template").Visible = True 
    ActiveWorkbook.Sheets("Template").Copy Before:=Worksheets(1) 
    ActiveSheet.Name = Name 
    Sheets(ActiveSheet.Name).Visible = True 
    ActiveWorkbook.Sheets("Template").Visible = xlVeryHidden 
    Application.ScreenUpdating = True 
    End If 
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Kala Shah Jahan
  • 11
  • 1
  • Please do not include links to external files in your question. – braX Aug 31 '21 at 04:12
  • @braX: Just curious. Is there a rule or a meta post which suggests that you cannot post a link to a sample file to external sources like dropbox or onedrive? I also do that and hence I am curious. Cheers – Siddharth Rout Aug 31 '21 at 06:56
  • Are you open to this [calendar option](https://stackoverflow.com/questions/54650417/how-can-i-create-a-calendar-input-in-vba-excel)? Also I do not see `lines marked with -*-*-*`? – Siddharth Rout Aug 31 '21 at 07:05
  • New users are limited to what we can do. Some forums allow links to outside sources, and some require the file to be uploaded. there are no lines with -*-*-* because when I added the code, I had to be compliant with forum rules on how the code was presented, so I had to remove the -*-*-* – Kala Shah Jahan Sep 02 '21 at 03:18

0 Answers0