Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    10

    Unanswered: Using a Userform to obtain value for use in macro

    Hi,

    Below is some code from a working macro that creates new worksheets, taking the header row from the main data page. At present the row to use is specified in the macro. Because the header row can appear on different rows in different reports, I want to include a simple userform so that the user can tell the macro which row on the main data sheet to use as the header row on the newly created sheets.

    Dim oSheet As Worksheet

    'Creates new sheet, pastes header row from data page to row 1
    Set oSheet = Worksheets.Add
    With oSheet
    .Name = "Sheet1"
    Rows("1:1").Select
    ActiveSheet.Paste

    I can create a simple user form with command buttons, and have it open when the macro is run. What I need to do is:

    Code the Cancel button to shut the macro, not just unload the userform.

    Code the Ok button to send the inputted value to the macro so that it is actioned in the above code, i.e the user selected row is pasted on the new worksheet.

    Many thanks.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Starsky
    Hi,

    Below is some code from a working macro that creates new worksheets, taking the header row from the main data page. At present the row to use is specified in the macro. Because the header row can appear on different rows in different reports, I want to include a simple userform so that the user can tell the macro which row on the main data sheet to use as the header row on the newly created sheets.

    Dim oSheet As Worksheet

    'Creates new sheet, pastes header row from data page to row 1
    Set oSheet = Worksheets.Add
    With oSheet
    .Name = "Sheet1"
    Rows("1:1").Select
    ActiveSheet.Paste

    I can create a simple user form with command buttons, and have it open when the macro is run. What I need to do is:

    Code the Cancel button to shut the macro, not just unload the userform.

    Code the Ok button to send the inputted value to the macro so that it is actioned in the above code, i.e the user selected row is pasted on the new worksheet.

    Many thanks.
    Hi

    As you seem to be not too familiar with UserForms (and using one can be a little trick to explain passing variable values to the calling code), I would suggest using the InputBox function wrapped in a function, thus
    Code:
    Function HeaderRowIsValid(ByRef HeaderRow As Long) As Boolean
        Dim Answer As String
        
        HeaderRowIsValid = False
        Do
            Answer = InputBox("Pease Enter Row Number of Header Roe tocopy from") 'GET NUMBER!
                    
            If Answer = "" Then Exit Function 'CANCEL BOTTON CLICKED
            
            If IsNumeric(Answer) Then 'IS IT A NUMBER
                If Answer Mod 1 <> 0 Then ' IS IT AN INTEGER
                    MsgBox "Row number entered is not an integer!"
                Else
                    If Answer < 1 Or Answer > 65336 Then 'IS IT IN THE VALID RANGE (Excel 2003)
                        MsgBox "The row number is not valid (less than 1 or is too large!"
                    Else
                        HeaderRow = CLng(Answer)
                        HeaderRowIsValid = True
                        Exit Function
                    End If
                End If
            Else
                MsgBox "Row number must numeric!"
            End If
        Loop
    End Function
    and used like this
    Code:
    Sub TestInputFunction()
        Dim Row As Long
        
        If Not HeaderRowIsValid(Row) Then Exit Sub
        
        MsgBox Row
    End Sub
    What do you think ?

    This does save you designing a form and programming the buttons etc., and you would still need all the validation code in the form (or elsewhere) anyway.

    MTB

  3. #3
    Join Date
    Apr 2009
    Posts
    10
    Thanks MTB. Let's see if I understand correctly. Your function is to handle input errors? Much appreciated. Do you know how I might get a correctly entered value into the new sheet code?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Starsky
    Thanks MTB. Let's see if I understand correctly. Your function is to handle input errors? Much appreciated. Do you know how I might get a correctly entered value into the new sheet code?
    Well no, the function is to get a valid row number from the user, but to do this you need to check that is is valid!

    In you code paste the HeaderRowIsValid() function in a Code Module. Then in the macro that does to worksheet inserting row copying etc, at the point where you want to get the row number from the user type this
    Code:
    Dim ThisHeadingRow as long 
    If Not HeaderRowIsValid(ThisHeadingRow) Then Exit Sub
    If a valid number is enered the code will contiue (with ThisheadingRow set to the user vale). If the user presses Cancel on the InpuBox when it will exit the current sub

    I am of on Hols now so unless I can get internet access where we are staying I will not be around until a week on Monday

    Cheers

    MTB

Posting Permissions

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