abarizqo
New Member
- Joined
- Sep 6, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello Everyone.. I'm new here. I've got some problem with my Excel app. I've build some userform containing about 90 Input Value from Combobox and TextBox. All I want is one single click from CommandButton that transfer all values in UserFrom to designated Cell in some Sheet with certain Criteria. Here is how the Form Look :
I've done some coding that Look like This :
As you can see, this kind of Code put a lot loading Time. All I need is, some repetition code so we can minimize the loading time. Each time I clicked "INPUT" command button, it needs about 40 second to transfer all 90 Input data to designated cell. I want to simplify the code but can't seem to find Any way. Tried the For Next i code but didn't work. Any help is appreciated everyone. Thanks.
I've done some coding that Look like This :
VBA Code:
Private Sub cmdinput_Click()
Application.ScreenUpdating = False
Set InputCap1 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis1.Value, LookIn:=xlValues)
Set InputCap2 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis2.Value, LookIn:=xlValues)
Set InputCap3 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis3.Value, LookIn:=xlValues)
Set InputCap4 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis4.Value, LookIn:=xlValues)
Set InputCap5 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis5.Value, LookIn:=xlValues)
Set InputCap6 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis6.Value, LookIn:=xlValues)
Set InputCap7 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis7.Value, LookIn:=xlValues)
Set InputCap8 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis8.Value, LookIn:=xlValues)
Set InputCap9 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis9.Value, LookIn:=xlValues)
Set InputCap10 = Sheet22.Range("B6:B1000").Find(WHAT:=Me.txtnis10.Value, LookIn:=xlValues)
If Me.cbhalaqoh.Value = "" _
Or Me.cbbulan.Value = "" Then
Call MsgBox("Harap isi Kode Halaqoh dan Bulan", vbInformation, "Kode Halaqoh")
ElseIf Me.TxtIndexBulan.Value = "0" Then
InputCap1.Offset(0, 5).Value = InputCap.TxtHE1.Value
InputCap1.Offset(0, 6).Value = InputCap.TxtH1.Value
InputCap1.Offset(0, 7).Value = InputCap.TxtS1.Value
InputCap1.Offset(0, 8).Value = InputCap.txtI1.Value
InputCap1.Offset(0, 9).Value = InputCap.TxtA1.Value
InputCap1.Offset(0, 10).Value = InputCap.cbtilawah1.Value
InputCap1.Offset(0, 11).Value = InputCap.txttilawah1.Value
InputCap1.Offset(0, 12).Value = InputCap.cbtahfizh1.Value
InputCap1.Offset(0, 13).Value = InputCap.txttahfizh1.Value
InputCap2.Offset(0, 5).Value = InputCap.TxtHE2.Value
InputCap2.Offset(0, 6).Value = InputCap.TxtH2.Value
InputCap2.Offset(0, 7).Value = InputCap.TxtS2.Value
InputCap2.Offset(0, 8).Value = InputCap.txtI2.Value
InputCap2.Offset(0, 9).Value = InputCap.TxtA2.Value
InputCap2.Offset(0, 10).Value = InputCap.cbtilawah2.Value
InputCap2.Offset(0, 11).Value = InputCap.txttilawah2.Value
InputCap2.Offset(0, 12).Value = InputCap.cbtahfizh2.Value
InputCap2.Offset(0, 13).Value = InputCap.txttahfizh2.Value
'and so on to InputCap10'
ElseIf Me.TxtIndexBulan.Value = "1" Then
InputCap1.Offset(0, 14).Value = Me.TxtHE1.Value
InputCap1.Offset(0, 15).Value = Me.TxtH1.Value
InputCap1.Offset(0, 16).Value = Me.TxtS1.Value
InputCap1.Offset(0, 17).Value = Me.txtI1.Value
InputCap1.Offset(0, 18).Value = Me.TxtA1.Value
InputCap1.Offset(0, 19).Value = Me.cbtilawah1.Value
InputCap1.Offset(0, 20).Value = Me.txttilawah1.Value
InputCap1.Offset(0, 21).Value = Me.cbtahfizh1.Value
InputCap1.Offset(0, 22).Value = Me.txttahfizh1.Value
'and so on to InputCap2'
'code continue with Index Criteria from 0 to 13'
End If
call MsgBox ("Congratulations! Data has been transfered succesfully.", vbInformation, "Success!")
'Clear all input'
As you can see, this kind of Code put a lot loading Time. All I need is, some repetition code so we can minimize the loading time. Each time I clicked "INPUT" command button, it needs about 40 second to transfer all 90 Input data to designated cell. I want to simplify the code but can't seem to find Any way. Tried the For Next i code but didn't work. Any help is appreciated everyone. Thanks.