Storing Data in Database Using 2 Different Methods Using Excel VBA

💡 Source Code

Dim ws As Worksheet, cmb1() As Variant, cmb1_cnt As Byte, cmb2() As Variant, cmb2_cnt As Byte

Private Sub UserForm_Activate()

With ComboBox1
.Value = "Select"
.AddItem "USA"
.AddItem "Denmark"
.AddItem "Finland"
End With

End Sub

Private Sub ComboBox1_Change()

Dim cmb1_txt As String, temp_txt As String
With ComboBox2
.Clear
If ComboBox1.Text = "USA" Then
.AddItem "New York"
.AddItem "San Francisco"
.AddItem "Los Angeles"
.AddItem "Dallas"
.AddItem "Seattle"
ElseIf ComboBox1.Text = "Denmark" Then
.AddItem "Copenhagen"
.AddItem "Roskilde"
.AddItem "Randers"
.AddItem "Aalborg"
ElseIf ComboBox1.Text = "Finland" Then
.AddItem "Helsinki"
.AddItem "Tampere"
.AddItem "Oulu"
.AddItem "Espoo"
.AddItem "Vaasa"
.AddItem "Kuopio"
End If
End With

End Sub

Private Sub ComboBox2_Change()

Dim cmb2_txt As String, temp_txt As String, i As Integer
If Not ComboBox2.Text = Empty Then
temp_txt = ComboBox1.Text + "-" + ComboBox2.Text
On Error Resume Next

For i = 1 To UBound(cmb2)
If Err.Number = 9 Then Err.Clear: Exit For
If cmb2(i) = temp_txt Then Exit Sub
Next i

cmb2_cnt = cmb2_cnt + 1
ReDim Preserve cmb2(1 To cmb2_cnt)
cmb2(cmb2_cnt) = temp_txt

For i = 1 To UBound(cmb2)
cmb2_txt = cmb2_txt + cmb2(i) + ","
Next i

cmb2_txt = Left(cmb2_txt, Len(cmb2_txt) - 1)
End If

Label2.Caption = cmb2_txt
On Error Resume Next

For i = 1 To UBound(cmb1)
If Err.Number = 9 Then Err.Clear: Exit For
If cmb1(i) = ComboBox1.Text Then Exit Sub
Next i

cmb1_cnt = cmb1_cnt + 1
ReDim Preserve cmb1(1 To cmb1_cnt)
cmb1(cmb1_cnt) = ComboBox1.Text

For i = 1 To UBound(cmb1)
cmb1_txt = cmb1_txt + cmb1(i) + ","
Next i

cmb1_txt = Left(cmb1_txt, Len(cmb1_txt) - 1)
Label1.Caption = cmb1_txt

End Sub

Private Sub CommandButton1_Click()

Dim i As Integer, j As Integer, last_row As Long, temp_txt As String, k As Integer
If ComboBox2.Text = Empty Then Exit Sub
Set ws = Worksheets("Method1")

For i = 1 To UBound(cmb1)
last_row = ws.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To UBound(cmb2)
k = InStr(1, cmb2(j), "-")
If Mid(cmb2(j), 1, k - 1) = cmb1(i) Then
temp_txt = temp_txt + Right(cmb2(j), Len(cmb2(j)) - k) + ","
End If
Next j

ws.Cells(last_row + 1, "A") = cmb1(i)
temp_txt = Left(temp_txt, Len(temp_txt) - 1)
ws.Cells(last_row + 1, "B") = temp_txt
temp_txt = Empty
Next i

End Sub

Private Sub CommandButton2_Click()

Dim i As Integer, j As Integer, last_row As Long, k As Integer
If ComboBox2.Text = Empty Then Exit Sub
Set ws = Worksheets("Method2")

For i = 1 To UBound(cmb1)
For j = 1 To UBound(cmb2)
last_row = ws.Cells(Rows.Count, 1).End(xlUp).Row
k = InStr(1, cmb2(j), "-")
If Mid(cmb2(j), 1, k - 1) = cmb1(i) Then
ws.Cells(last_row + 1, "A") = cmb1(i)
ws.Cells(last_row + 1, "B") = Right(cmb2(j), Len(cmb2(j)) - k)
End If
Next j
Next i

End Sub
---------------------------------------------------------------------------------------

Buy a Cofee ☕ for theworldofalgorithms: https://ko-fi.com/theworldofalgorithms

🥰Thank you for the donation. I am deeply grateful for your help!🥰
----------------------------------------------------------------------------------------
#excel
#msexcel
#msexceltutorial
#msexcelcourse
#vba
#vbatutorial
#excelvba
#fulltutorial
#excelvbatutorial
#microsoftexceltutorial
#tutorial
#exceltutorial
#advancedexcel
#advancedexceltraining
#tutor
#exceltutor
#exceltutoring
#exceltutoringonline
#onlineexceltutoring
#algorithm
#algorithms
#programming
#program
#script
#vbamacro
#vbamacros
#usingmacro
#veryuseful
#useful
#veryusefulmacro
#freeeducation
#freeprogramming
#freelancer
#excelfreelancer
#vbafreelancer
#freelanceservices
#microsoftofficespecialist
#microsoftexcelexpert
#vbauserform
#excelvbauserform
#combobox
#vbacombobox
#dependentcomboboxes
#label
#vbalabel
#commandbutton
#vbacommandbutton
#data
#datasotrage
#database
#excelvbadatabase
#vbadatabase
#datastoringmethods