force button to check the fields before copy

Mayozero

New Member
Joined
May 16, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Good Morning,

i just started to learn VBA lately, so please be patient with me in my silly questions ^_^

1st question is :
How can i force the UserForm to check for the empty fields before the command button approve the task?
in this example i set 3 options in the combobox1
any idea how to get this code fixed ?

Below is the command button code function :

VBA Code:
Function copy_from_form_without_repeat_LoginB1()
Dim rng1 As Range
Dim str_search As String
str_search = TextBox1.Value
ActiveWorkbook.Sheets("Database").Activate
Set rng1 = Sheets("Database").Range("A:A").Find(str_search, , xlValues, xlWhole)
If rng1 Is Nothing Then
Dim LastRow As Long
LastRow = ActiveWorkbook.Sheets("Database").Range("A1000000").End(xlUp).Row
LastRow = LastRow + 1
With ActiveWorkbook.Sheets("Database")
.Range("A" & LastRow).Value = TextBox1.Value
.Range("B" & LastRow).Value = ComboBox1.Value
.Range("C" & LastRow).Value = TextBox2.Value
End With
Else
MsgBox str_search & "  is already listed"
End If
End Function

2nd question:
Hot to hide the first row in the ListBox1 when i call the function
and here is the code below too ^_^

VBA Code:
Function show_data_in_listbox_test()
    Dim LastRow As Long, Row As Long, Col As Long, TempArray
    With Sheets("Database")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        TempArray = .Range("A1:F" & LastRow).Value
        For Row = 1 To LastRow
            For Col = 3 To 6 '---select.name.for.the.columns; from 3 to 6 with Col= 3:6'
                TempArray(Row, Col) = Format(TempArray(Row, Col), "h:mm:ss")
            Next Col
             TempArray(Row, 2) = Format(TempArray(Row, 2), "[$-x-sysdate]dddd, mmmm dd, yyyy")
        Next Row
    End With
    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "145,80,75,90,80,80"
        .List = TempArray
    End With
End Function

3nd and last question is : How i set the default option for combobox1 when the userform initialized and call the combobox function unless i change it?
below the code and after the initializa code
VBA Code:
Function items_from_code_to_combobox1()
ComboBox1.AddItem "Normal"
ComboBox1.AddItem "Weekend"
ComboBox1.AddItem "Holiday"
End Function

last one below:

VBA Code:
Private Sub UserForm_Initialize()
Call items_from_code_to_combobox1
Call show_data_in_listbox_test
'next
On Error Resume Next
Me.TextBox1 = Format(CDate(Me.TextBox1), "[$-x-sysdate]dddd, mmmm dd, yyyy")
'end next
'\
TextBox1.Value = Format(Date, "d/m/yyyy") ''' this is the true date format
Application.Run "clock1"
TextBox2.Value = Format(Now, "[$-x-systime]h:mm:ss AM/PM")
Application.Run "clock1"
'\
End Sub


Thanks alot in advance 😘😘
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
1st question is :
How can i force the UserForm to check for the empty fields before the command button approve the task?
Check if the combo box selection is blank (vbnullstring = "")
VBA Code:
    If ComboBox1.Value = vbNullString Then
        MsgBox "Selection is blank!"
    End If


2nd question:
Hot to hide the first row in the ListBox1 when i call the function
Not quite sure what you mean with this question.


3nd and last question is : How i set the default option for combobox1 when the userform initialized and call the combobox function unless i change it?
After you initialize all of the values in your ComboBox, add
VBA Code:
ComboBox1.ListIndex = x
where x is the number of the item from your list you want to be default - i.e x=0 for the first value in the list, x=1 for the second, etc.
 
Solution

Forum statistics

Threads
1,143,687
Messages
5,720,301
Members
422,275
Latest member
Maria95

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
Top