Using a Userform to obtain value for use in macro

Starsky · #1 (**permalink**) 06-11-09, 06:28

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.

MikeTheBike · #2 (**permalink**) 06-11-09, 08:32

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

Starsky · #3 (**permalink**) 06-11-09, 14:36

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?

MikeTheBike · #4 (**permalink**) 06-11-09, 16:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech