Multipage user form and how to insert each user form into different sheets ?

[ellyna]

I'm using multipage userform and have different sheet in excel. I have no idea how to use the vba to insert the second userform into 2nd sheet of excel.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[dmt32]

Hi,
found a little time to have a play with your project - see if updated file does what you want

Dave

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ellyna]

Hi,
found a little time to have a play with your project - see if updated file does what you want

Dave

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Hi Dave, i notice that are you using one user form for each department ? because i would like to use multipage user form but each of it will be store at different sheets of database in excel.

How can i download your excel file as i want to explore the code?
thank you

 

[ellyna]

Hi Dave, i notice that are you using one user form for each department ? because i would like to use multipage user form but each of it will be store at different sheets of database in excel.

How can i download your excel file as i want to explore the code?
thank you

Hi Dave, i manage to download the file. Thank you. I will explore the code that you have help me.

 

[ellyna]

Hi,
found a little time to have a play with your project - see if updated file does what you want

Dave

Dropbox - File Deleted - Simplify your life

www.dropbox.com

hi Dave, thank you very much as you help me as i wanted it to save for each multipage into the sheets based on the department.
As i am new to vba , i can see if i insert the value and it will save as textbox1,textbox2 and extra. How can i put my script as i want it to insert a actual value ?
This is the vba that you help me and i have edit on the sheet of "summary".

Thank you can you help me as i am new . And if i wanted the date to display automatically in every user form just as in the packing multi page.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[dmt32]

hi Dave, thank you very much as you help me as i wanted it to save for each multipage into the sheets based on the department.
As i am new to vba , i can see if i insert the value and it will save as textbox1,textbox2 and extra. How can i put my script as i want it to insert a actual value ?
This is the vba that you help me and i have edit on the sheet of "summary".

Thank you can you help me as i am new . And if i wanted the date to display automatically in every user form just as in the packing multi page.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Hi,
Glad code does largely what you want.
Before posted link I neglected to amend code line I was using for testing purposes

If you go to the AddRecord_Code module you need to amend the line shown in BOLD

'index each control on selected page
    For i = 1 To ActivePage.Controls.Count
        'loop through each textbox or combobox control on page
        For Each ctrl In ActivePage.Controls
            'to ensure each control posts data in correct order,
            'we check if control tabindex matches index value
            If ctrl.TabIndex = i - 1 Then
                If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                    'post record to range
                    With NewRecord.Offset(, IIf(c > 1, c + 1, c))
                        'check for date format & value
                        If ctrl.Value Like "##/##/####" And IsDate(ctrl.Value) Then
                            .Value = DateValue(ctrl.Value)
                        Else
'AMENED THIS LINE
                            .Value = ctrl.Name
'To This
                          .Value = ctrl.Value

                        End If
                    End With
                    'clear control
                    ctrl.Value = ""
                    c = c + 1
                End If
            End If
        Next ctrl
    Next i

Dave

 

[dmt32]

Hi Dave, i notice that are you using one user form for each department ? because i would like to use multipage user form but each of it will be store at different sheets of database in excel.

Its just then one userform you posted in your project - only thing I have done is to size the form based on the multipage tab selected.

Dave

 

[ellyna]

Hi,
Glad code does largely what you want.
Before posted link I neglected to amend code line I was using for testing purposes

If you go to the AddRecord_Code module you need to amend the line shown in BOLD

'index each control on selected page
    For i = 1 To ActivePage.Controls.Count
        'loop through each textbox or combobox control on page
        For Each ctrl In ActivePage.Controls
            'to ensure each control posts data in correct order,
            'we check if control tabindex matches index value
            If ctrl.TabIndex = i - 1 Then
                If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                    'post record to range
                    With NewRecord.Offset(, IIf(c > 1, c + 1, c))
                        'check for date format & value
                        If ctrl.Value Like "##/##/####" And IsDate(ctrl.Value) Then
                            .Value = DateValue(ctrl.Value)
                        Else
'AMENED THIS LINE
                            .Value = ctrl.Name
'To This
                          .Value = ctrl.Value

                        End If
                    End With
                    'clear control
                    ctrl.Value = ""
                    c = c + 1
                End If
            End If
        Next ctrl
    Next i

Dave

Thank you so much for the help. If there is multiple sheet so i just put all of the script at the bold amend line ?
Or do i need to state the value to which sheet to be stored. Please guide me thank you.

 

[dmt32]

Thank you so much for the help. If there is multiple sheet so i just put all of the script at the bold amend line ?
Or do i need to state the value to which sheet to be stored. Please guide me thank you.

Hi,
I updated file for you:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave

 

[ellyna]

Hi,
I updated file for you:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave

Hi Dave thank you so much for hte help just as i wanted. But i noticed in sheet summary when i insert the value it suppose to be recorded on row 3 instead but it record in row 2.
May i know which script did you edit ? I'm a bit clueless as i never do multipage excel vba before.
May i ask you if i want to add the calculation script where can i edit ?
Thank you sorry for burdening you on my excel. I'm still learning from YouTube and forum to guide me as this is my internship project.