UserForm - populate data in different Forms from one excel sheet

BluEEyE86

New Member
Joined
May 25, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hello Team,

I'm counting for your support & experiance again. I'm going to prepare autoamted excel file for one of the my process which use two different userform but both will use the worksheet with the same data. First one from the top will be using for registration, button from the buttom just to update the status.

Main.JPG


When I'm going to register something, the screen looks like below & it works properly:

Reg.JPG

Above screen is using below code:

VBA Code:
Sub Reset()
    
    Dim iRow As Long
    
    iRow = [Counta(Database!A:A)] ' indentifying the last row
    
    With mapFORM
        
        '.cbParameter.Value = ""
        .cmbType.Clear
            
        .txtRollNo.Value = ""
            
        .cmbType.AddItem "Seria"
        .cmbType.AddItem "TSR"
        .cmbType.AddItem "Capability"
        '.cmbType.AddItem "Lam D"
                    
        .txtSSM.Value = ""
        .txtSFM.Value = ""
        .txtRFS.Value = ""
        .txtWIni.Value = ""
    
        .lstDatabase.ColumnCount = 11
        .lstDatabase.ColumnHeads = True
        
        .lstDatabase.ColumnWidths = "60,60,80,60,60,120,300,60,60,120,60"
        
        If iRow > 1 Then
        
            .lstDatabase.RowSource = "Database!A2:K" & iRow
        Else
        
            .lstDatabase.RowSource = "Database!A2:K2"
        End If
    
    End With
    
    'mapFORM.Hide
    
End Sub

I have a problem when I'm trying to update after clicking of bottom button. I'd like to have similar screen like above but list isn;t up to date:

Up.JPG


It is related to below code:

VBA Code:
Sub Reset_2()
    
    Dim iRow As Long
    
    iRow = [Counta(Database!A:A)] ' indentifying the last row
    
    With updateFORM
    
    .txtMFG.Value = ""
        
    .cmbStatus.AddItem "Otwarte"
    .cmbStatus.AddItem "Zwolnione"
    .cmbStatus.AddItem "Odrzucone"
    .cmbType.AddItem "TAK"
    .cmbType.AddItem "NIE"
                
    .txtComment.Value = ""
    .txtApprover.Value = ""

        
        .lstDatabase.ColumnCount = 11
        .lstDatabase.ColumnHeads = True
        
        .lstDatabase.ColumnWidths = "60,60,80,60,60,120,300,60,60,120,60"
                
        If iRow > 1 Then
        
            .lstDatabase.RowSource = "Database!A2:K" & iRow
        Else
        
            .lstDatabase.RowSource = "Database!A2:K2"
        
        End If
    
    End With
    
End Sub

When I'm starting with code, I have below issue & don't have idea how to fix it

Error.JPG


Hope everything is clear enough. If you need me to share the file, just please let me know.

Thank you for all answers & support
 

Attachments

  • Main.JPG
    Main.JPG
    42.1 KB · Views: 9
  • Reg.JPG
    Reg.JPG
    76.4 KB · Views: 8

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
This error message is because the control "lstDatabase" does not exist in the form "updateFORM", could you tell us, what is the name of the listbox of the form "updateFORM".
 
Upvote 0
Sorry for late answer but I had limited access due to holiday.

I have changed idea for this but I have next problem. I don't know if I should open new topic but I will describe it here.

I working with one UserForm and I have couple fields in two frames. Left side is for registering of new order which should appear if written number in TOP field already doesn't exist on the list. On the right side is "status update" if order is already registered (screen below - all fields are hidden):

Main.JPG


The issue is that when I writting some number in the TOP field which doesn't exist in third column then fields in left frame aren't visible.

Main.JPG


Here is my code "Get Data"

VBA Code:
Sub GetData()

Dim id As String, i As Integer, sh As Worksheet

If IsNumeric(mapFORM.txtRollNo.Value) Then
    
    i = 0
    Set sh = ThisWorkbook.Sheets("Database")
    id = mapFORM.txtRollNo.Value
    
    
    
    Do While sh.Cells(i + 1, 3).Value <> ""
        
        If sh.Cells(i + 1, 3).Value = id Then
            
            Select Case sh.Cells(i + 1, 8).Value
                                 
            Case "Otwarte"
                
                mapFORM.Frame3.Visible = True
                mapFORM.UpdateButton.Visible = True

            
            Case Else
                
                mapFORM.Frame3.Visible = False
                mapFORM.Frame4.Visible = False
                mapFORM.UpdateButton.Visible = False
                mapFORM.SubmitButton.Visible = False
                mapFORM.txtRollNo = ""
                id = ""
                MsgBox ("Zlecenie zostało już przebadane")
                                
            End Select
            
        ElseIf sh.Cells(i + 1, 3).Value <> id Then
            
            Select Case sh.Cells(i + 1, 8).Value
                                 
                Case Is = ""
                
                mapFORM.Frame4.Visible = True
                mapFORM.SubmitButton.Visible = True

             
            End Select
            
            
        ElseIf sh.Cells(i + 1, 3).Value Is Empty Then
           
            mapFORM.Frame3.Visible = False
            mapFORM.Frame4.Visible = False
            mapFORM.UpdateButton.Visible = False
            mapFORM.SubmitButton.Visible = False

            
        End If
        
        i = i + 1

    Loop

End If

End Sub
 

Attachments

  • Main.JPG
    Main.JPG
    125.6 KB · Views: 6
Upvote 0
It is a different problem. Create a new thread.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top