[RESOLVED] Restrict the input value of a message box

[susiecat]

Hi everyone,

I am quite new to the VB programming world but in a few days I learnt quite a lot

I want to create a message box that would pop up after a click on a button in sheet1 and according to the input value x, the macro will copy x-1 times the sheet 2.

I wrote this code but no matter the number between 2 and 4, it will always copy only once the sheet2. For x=1, no copy is needed.

Sub NbOfSheet2ToCopy()
x = InputBox("How many copies of Sheet2 would you like? Please enter a number between 1 and 4")
If x >= 1 Or x <= 4 Then
numtimes = x - 1
Sheets("Sheet2").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")
Sheets("Sheet2").Select
Else: MsgBox ("Please enter a number between 1 and 4")
End If
End Sub

Is it possible to have the copied sheets in order ?

Thank you for your help !

[Hack]

Welcome to the forums.

You are getting only one copy because you are only telling it to make one copy.

It can only make one copy at a time, so if you need more than one you need to send that code through a loop. The number of desired copies would correspond to the number of times it would go through the loop.

Dim i As Integer
For i = 1 to x
   'do your copy stuff here
Next

[elleg]

As Hack said, you need a loop to get more than one copy. And, to answer your other question, yes, it is definitely possible to copy the sheets in order. Below is a sample procedure that will do what I think you want to do. (By the way, if you copy and paste the code below, it will need to go at the top of the module, as the line 'Option Explicit' must always be at the very top of a module).

Option Explicit 'Force declaration of all variables

'PUBLIC/CONSTANT VARIABLE DECLARATIONS------------------------------------------------------

'PROCEDURES----------------------------------------------------------------------------------
Sub CopySheets()
'---PROCEDURE DESCRIPTION/COMMENTS-----------------------------------------------------------

'---VARIABLE DECLARATIONS--------------------------------------------------------------------
    Dim x As Variant 'any type of value can be saved in this variable
    Dim bIsInteger1to4 As Boolean
    Dim iNumTimes As Integer 'small whole number variable
    Dim iIndex As Integer 'small whole number variable
    Dim shtCopyMe As Worksheet 'variable to store a worksheet
'---CODE-------------------------------------------------------------------------------------
    'you can change the name of the sheet below to whichever sheet you want to copy
    Set shtCopyMe = ActiveWorkbook.Sheets("Sheet2") 'must use set because a wksht is an object
    iIndex = shtCopyMe.Index 'get the index of the sheet to copy
    
    Do 'loop to catch error (error being input values that are not integers)
        bIsInteger1to4 = False 'boolean to indicate whether the provided value is an integer
        x = InputBox("How many copies of " & shtCopyMe.Name & " would you like? Please enter a number between 1 and 4")
        If IsNumeric(x) Then 'check if x is a number
        'if we convert x to an integer, is it still equal to x's original value?
            If CInt(x) = x Then 'if true, x is an integer
                If x >= 1 Or x <= 4 Then 'check if x is between 1 and 4
                    bIsInteger1to4 = True 'if it is, we're good, set this boolean to true
                End If
            End If
        End If
    Loop Until bIsInteger1to4 = True 'will loop until a value of the proper type is provided
    
    iNumTimes = x - 1 'number of times to copy the sheet
    
    
    For x = 1 To iNumTimes
        'here we'll refer to the sheet to copy by it's index, as stored in iIndex
        'iIndex + x - 1 gives us the index of the most recent copy (or, for the first
        'iteration of the loop, gives us just iIndex
        Sheets(iIndex).Copy After:=ActiveWorkbook.Sheets(iIndex + x - 1)
        Sheets(iIndex).Activate
    Next x
End Sub

[susiecat]

Thank you both of you for your time!

Elleg, your code works smoothly, and your comments really helped me to understand it.

This is incredible but the more I learn the more I find programming interesting.

[elleg]

I know what you mean =). When I first discovered programming, I just enjoyed playing around with it to see what I could do and what I could figure out. (Heh, I still do...)

I'm glad the code worked for you. I'm sure you can figure out ways to expand upon that code to, for example, have the user decide which sheet they want to copy, etc. Good luck!