VBA UserForm - two objects appeared (Frames) apart of only one selected one based on example

B

BluEEyE86

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

During my project, I met a problem which I will try to clearly explain.

I have one User Form where I want to register new order & update the status. I have created two frames within this User Form with couple fields. On the bottom, there is an list of orders & their status like open, approved or rejected. My idea is to write in the text box number. Based on this text, list from the bottom is searching. If number doesn't exist on the list then left Frame appeared to register new order but when order number exist on the list then right Frame needs to appeared to update status of this order. Hopefuly below pictures will explain better above words.

1) Here I'm wiritng number which isn;t exist on the list & left frame appeared as I expected - this is OK



Main.JPG



2) Now, I'm trying to change status of row number 2 with order 1040202 and after Enter, both frames appeared but I need only this from the right side (This is my problem)

Main.JPG


Here is my VBA code:

VBA Code: 
Sub GetData()

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

If IsNumeric(mapFORM.txtRollNo.Value) Then
    
    i = 1
    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.Frame4.Visible = False
                mapFORM.UpdateButton.Visible = True
                mapFORM.SubmitButton.Visible = False
            
            Case Else
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    mapFORM.SubmitButton.Visible = False
                    
                End If
                                
            End Select
            
        ElseIf sh.Cells(i + 1, 3).Value <> id Then
        
                mapFORM.Frame3.Visible = False
                mapFORM.Frame4.Visible = True
                mapFORM.SubmitButton.Visible = True
                mapFORM.UpdateButton.Visible = False
            
        End If
        
        i = i + 1

    Loop

End If

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Use the following to enable or disable a Frame. Think it through ...

VBA Code: 
Option Explicit

Private Sub CommandButton1_Click()

Dim ctl As MSForms.Control

    For Each ctl In Me.Frame1.Controls
        ctl.Enabled = Not ctl.Enabled ' toggle Enabled property, use True/False if you don't want to toggle
    Next ctl
End Sub
 
Upvote 0
Thanks Logit, I will consider your solution. I'm trying to solve it as well. I realized that when I will write order no from last row, everything is correct like below:

Main.JPG


But when I will write order number from first or second row then both frames appeared at the same time. Based on VBA code it shouldn't but ther may be some error.

Main.JPG

This is working like this only when I will exclude Frame4 from the code by " ' ". When code with Frame4 is active, only this frame is appeared even if is "False".

USerForm code:

VBA Code: 
Private Sub UserForm_Initialize()
        
    Call Reset
    mapFORM.txtRollNo.SetFocus
    
    mapFORM.Frame3.Visible = False
    mapFORM.Frame4.Visible = False
    mapFORM.SubmitButton.Visible = False
    mapFORM.UpdateButton.Visible = False
    
End Sub

TextBox code where I'm writing order number:

VBA Code: 
Private Sub txtRollNo_AfterUpdate()

    Call GetData

End Sub

GetData code with excluded Frame4:

VBA Code: 
Sub GetData()

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

If IsNumeric(mapFORM.txtRollNo.Value) Then
    
    i = 1
    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.Frame4.Visible = False
                mapFORM.UpdateButton.Visible = True
                'mapFORM.SubmitButton.Visible = False
            
            Case Else
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    'mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    'mapFORM.SubmitButton.Visible = False
                    
                End If
                                
            End Select
            
        ElseIf sh.Cells(i + 1, 3).Value <> id Then
        
                'mapFORM.Frame3.Visible = False
                mapFORM.Frame4.Visible = True
                mapFORM.SubmitButton.Visible = True
                'mapFORM.UpdateButton.Visible = False
            
        End If
        
        i = i + 1

    Loop

End If

End Sub
 
Upvote 0
Simply, I have replaced Do Untill Loop for Range & code is working as I want.

VBA Code: 
Sub GetData()

Dim id As String, i As Integer, sh As Worksheet, msgValue As VbMsgBoxResult, id2 As String, cell As Range, rng As Range, Status As String


If IsNumeric(mapFORM.txtRollNo.Value) Then
    
    i = 1
    Set sh = ThisWorkbook.Sheets("Database")
    id = mapFORM.txtRollNo.Value
    
    Set rng = sh.Columns("C:C")
    
    Set cell = rng.Find(What:=id, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
    
    If cell Is Nothing Then
                
                mapFORM.Frame3.Visible = False
                mapFORM.Frame4.Visible = True
                mapFORM.SubmitButton.Visible = True
                mapFORM.UpdateButton.Visible = False
                mapFORM.Label23.Visible = False
                mapFORM.Label24.Visible = False
                mapFORM.cbTSRname.Visible = False
                mapFORM.txtTSRnumber.Visible = False
                        
    Else
            
            sh.Activate
                        
            Cells.Find(What:=mapFORM.txtRollNo, After:=ActiveCell, LookIn:=xlValues).Activate
            
            Status = ActiveCell.Offset(0, 5)
            ThisWorkbook.Sheets("Main").Activate
            
            Select Case Status
                                 
            Case "Otwarte"
            
                mapFORM.Frame3.Visible = True
                mapFORM.Frame4.Visible = False
                mapFORM.UpdateButton.Visible = True
                mapFORM.SubmitButton.Visible = False
                
                With mapFORM.cmbStatus
                .List = Array("Otwarte", "Decyzja", "Retest", "Zwolnione", "Odrzucone", "Zamknięte")
                .ListIndex = 0
                End With
                  
            Case "Decyzja"
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    mapFORM.SubmitButton.Visible = False
                    
                    With mapFORM.cmbStatus
                    .List = Array("Otwarte", "Decyzja", "Retest", "Zwolnione", "Odrzucone", "Zamknięte")
                    .ListIndex = 1
                    End With
                End If
                
            Case "Retest"
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    mapFORM.SubmitButton.Visible = False
                    
                    With mapFORM.cmbStatus
                    .List = Array("Otwarte", "Decyzja", "Retest", "Zwolnione", "Odrzucone", "Zamknięte")
                    .ListIndex = 2
                    End With
                End If
                    
              Case "Zwolnione"
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    mapFORM.SubmitButton.Visible = False
                    
                    With mapFORM.cmbStatus
                    .List = Array("Otwarte", "Decyzja", "Retest", "Zwolnione", "Odrzucone", "Zamknięte")
                    .ListIndex = 3
                    End With
                End If
                                
              Case "Odrzucone"
                
                msgValue = MsgBox("Zlecenie zostało już przebadane. Czy chcesz zmienić status tego zlecenia ?", vbYesNo + vbInformation, "Confirmation")
    
                If msgValue = vbYes Then
                
                    mapFORM.Frame3.Visible = True
                    mapFORM.Frame4.Visible = False
                    mapFORM.UpdateButton.Visible = True
                    mapFORM.SubmitButton.Visible = False
                    
                    With mapFORM.cmbStatus
                    .List = Array("Otwarte", "Decyzja", "Retest", "Zwolnione", "Odrzucone", "Zamknięte")
                    .ListIndex = 4
                    End With
                End If
                    
            End Select
            
    End If
       
    Worksheets("Main").Activate

End If

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Solved
Select one row with same ID in userform listbox of quantity edit.
Replies
7
Views
2K
EXCEL MAX
EXCEL MAX
A
  • Question
VBA to save file with a different name and / or append suffixes based on conditions
Replies
1
Views
925
akshay27
A
C
  • Question
Two VBA macros - one appears to not work once a change is made
Replies
1
Views
191
caet_
C
S
  • Question
Filtered data in Listbox based on Option buttons EXCEL VBA
Replies
4
Views
4K
alansidman
A
H
  • Question
Calendar userform-text selection
Replies
1
Views
6K
hissonrr
H

Forum statistics

Threads
1,215,022
Messages
6,122,721
Members
449,093
Latest member
Mnur

Share this page

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