my excel vba stop running when i run the userform and not responding

[Aihmar]

hi

Good Day!

i dont know whats wrong with my excel codes.

when i run will stop and excel not responding:crying:

[paulked]

Hi and welcome to the forum.

As for an answer... Hmm.... I'm afraid we don't know either.

Please provide more information and post the code that is failing (use the 'code tags' to wrap your code, the # button).

Cheers

Paul Ked

[Aihmar]

#Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet3")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow 'if remove this FOR the userform show up'
DoEvents
Me.cmbiqama.AddItem ws.Cells(i, "B").Value
Next i
lblpayd.Caption = Format(Date, "Medium Date")
ComboBox2.SetFocus
textBASICSALARY.Value = 0
textOVERTIME.Text = 0
textFOOD.Text = 0
textTRANS.Text = 0
textOTHERS.Text = 0
txtab.Text = 0
txtlate.Text = 0
txtloan.Text = 0
txtpenalty.Text = 0
opbcar = False
opbwcn = False
opboutcon = False
With ComboBox1
.AddItem "MALE"
.AddItem "FEMALE"
End With
With ComboBox2
.AddItem "6"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "12"
End With
With cmbxcountry
.AddItem "BANGLADESH"
.AddItem "EGYPTIAN"
.AddItem "FILIPINO"
.AddItem "INDIAN"
.AddItem "NEPALI"
.AddItem "MORROCO"
.AddItem "SAUDI"
.AddItem "SRI LANKA"
.AddItem "SUDANESE"
.AddItem "SYRIAN"
.AddItem "PAKISTAN"
.AddItem "TUNIZA"
.AddItem "YEMENI"
End With
End Sub

[Aihmar]

what's wrong with my FOR code?

[paulked]

Remove the DoEvents, that may be causing it to hang

[Aihmar]

what's wrong with my FOR code?

i have removed the DoEvents still the userform1 still not showing

[paulked]

1. Did it stop the hanging?

2. What are you using to call the UserForm Show?

[Aihmar]

1. it worsen excel now not responding

2. when i press run or F5 excel not responding

[Aihmar]

i cannot upload the file because it is larger than 1MB. 1.2MB to be exact.:crying:

[paulked]

Zip the file, then try

[Aihmar]

i zip the file still exceeds the limit:crying::crying::help

[paulked]

You could either:

1. Take out any objects and stuff that is not required for the working (pictures etc) and save as a temporary file for upload,
2. Save it to dropbox (or similar) and provide a link or
3. Try save as an xlsb file, thay can be a bit smaller.

[Aihmar]

https://www.dropbox.com/s/z4vbtzb4ly2kv9m/jouna%20payroll.xlsm?dl=0 .this is the link to the file

[paulked]

My first question, probably of many, is WHY have you got 15,856 columns for employee data?

My flabber has never been so ghasted!

[paulked]

21498

1,048,559 rows for employees? That's quite a big company.

I've shrunk your table to fit the data you have in it leaving a few columns for future.

I've taken out the 2 Global Dim's that are causing confusion.

I've removed the picture, pretty as it was, from sheet 4 to make the file size fit here. You can put that back in by all means.

I've taken out the DoEvents which was superfluous.

The form will open now, but i couldn't view all of it because of its size.

[Aihmar]

thats was my mistake hahaha. I have tried to change the stable colors and style. but it also changes the range automatically, i don't know why

thank you very much for your help! very much appreciated!

and my update button not working properly. i dont know whats wrong with my coding

[Aihmar]

where is my mistake in the codings?
thank you very much. you saved me .

[SamT]

Run this code in a new workbook

Public Sub ResetApplication()
'Resets some application properties to default values

With Application
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
.EnableCalculation = True
.Calculation = xlCalculationAutomatic
'And any other you might think of
End With
End sub

[paulked]

where is my mistake in the codings?
thank you very much. you saved me .

I haven't the time to go through it, sorry, but if you step through each sub you'll find them, then post the errors here.

[Aihmar]

IQAMANo = Me.cmbiqama.Value
Sheet3.Select
Dim rowselect As Integer
rowselect = Me.cmbiqama.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxcountry.Value
Cells(rowselect, 7) = Me.ComboBox2.Value
Cells(rowselect, 7) = Me.textBASICSALARY.Value
Cells(rowselect, 9) = Me.textFOOD.Value
Cells(rowselect, 10) = Me.textTRANS.Value
Cells(rowselect, 11) = Me.textOTHERS.Value
Cells(rowselect, 8) = Me.textOVERTIME.Value
Cells(rowselect, 13) = Me.txtab.Value
Cells(rowselect, 14) = Me.txtlate.Value
Cells(rowselect, 15) = Me.txtloan.Value
Cells(rowselect, 16) = Me.txtpenalty.Value
Cells(rowselect, 17) = Me.lbldeduc.Caption
Cells(rowselect, 19) = Me.lblGROSSPAY.Caption
Cells(rowselect, 20) = Me.lblnetpay.Caption
Cells(rowselect, 23) = Me.txtemployer.Value
Cells(rowselect, 24) = Me.ComboBox2.Value


my update button does not update insted creating new data in a new row .
the date is in row 166 when update it goes to row 319

please help im confused.



thank you very much :help: pray2::dunno

[paulked]

As I said, step through the sub checking each step. Clearly this isn't correct:



Cells(rowselect, 7) = Me.ComboBox2.Value
Cells(rowselect, 7) = Me.textBASICSALARY.Value


But that is not the problem you are experiencing. The code you've posted doesn't really tell us much. What value is in me.cmbiqama?

[Aihmar]

Private Sub cmbiqama_Change()

Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("sheet3")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.cmbiqama.Value) = ws.Cells(i, "B") Then
Me.Textempnum = ws.Cells(i, "B").Value
Me.txtiqama = ws.Cells(i, "C").Value
Me.txtemployeename.Value = ws.Cells(i, "D").Value
Me.ComboBox1 = ws.Cells(i, "E").Value
Me.cmbxcountry = ws.Cells(i, "F").Value
Me.textBASICSALARY = ws.Cells(i, "G").Value
Me.textOVERTIME = ws.Cells(i, "H").Value
Me.textFOOD = ws.Cells(i, "I").Value
Me.textTRANS = ws.Cells(i, "J").Value
Me.textOTHERS = ws.Cells(i, "K").Value
Me.txtemployer = ws.Cells(i, "W").Value
Me.ComboBox2 = ws.Cells(i, "X").Value


End If
Next i


End Sub







Private Sub UserForm_Initialize()


Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet3")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Me.cmbiqama.AddItem ws.Cells(i, "B").Value
Next i







this is the value

[Aihmar]

Dim IQAMANo As String


IQAMANo = Me.cmbiqama.Value



With Sheet3.Rows(IQAMANo)

.Cells(1) = Me.lblPayref.Caption
.Cells(2) = Me.Textempnum.Value
.Cells(3) = Me.txtiqama.Value
.Cells(4) = Me.txtemployeename.Value
.Cells(5) = Me.ComboBox1.Value
.Cells(6) = Me.cmbxcountry.Value
.Cells(7) = Me.textBASICSALARY.Value
.Cells(8) = Me.textOVERTIME.Value
.Cells(9) = Me.textFOOD.Value
.Cells(10) = Me.textTRANS.Value
.Cells(11) = Me.textOTHERS.Value
.Cells(13) = Me.txtab.Value
.Cells(14) = Me.txtlate.Value
.Cells(15) = Me.txtloan.Value
.Cells(16) = Me.txtpenalty.Value
.Cells(17) = Me.lbldeduc.Caption

.Cells(19) = Me.lblGROSSPAY.Caption
.Cells(20) = Me.lblnetpay.Caption

.Cells(22) = Me.lblpayd.Caption
.Cells(23) = Me.txtemployer.Value
.Cells(24) = Me.ComboBox2.Value



End With





my recoded update button still not working

[paulked]

this is the value

I don't see a value?

I you are not sure how to get this put this line:



MsgBox IQAMANo


in between these two lines:



IQAMANo = Me.cmbiqama.Value



With Sheet3.Rows(IQAMANo)

[Aihmar]

I don't see a value?

I you are not sure how to get this put this line:



MsgBox IQAMANo


in between these two lines:



IQAMANo = Me.cmbiqama.Value



With Sheet3.Rows(IQAMANo)


my values are based on the combo box.
I populate textbox and others using combo box value based on the database.
the value is on the date base B column the ID number. populate using combox

[Aihmar]

i have uploaded the filrfor reference : pray2:: pray2:: pray2:: pray2:

[paulked]

21507

It worked for me!

I'm the new employee at the bottom of the list.

[Aihmar]

21507

It worked for me!

I'm the new employee at the bottom of the list.



welcome to my company paul :)

the register button works but the update button does not work as it should be :(

[paulked]

21509

The update worked too. I updated myself and there I am, row 513 sheet5.

[Aihmar]

21509

The update worked too. I updated myself and there I am, row 513 sheet5.


it should update the current data of employee but it will create another same data in the other row

[paulked]

Private Sub cmdupdate_Click()
Dim IQAMANo As Integer
If Me.cmbiqama.Value = "" Then
MsgBox "IQAMA No Can Not be Blank!!!", vbExclamation, "IQAMA No"
Exit Sub
End If
IQAMANo = Me.cmbiqama.Value
Sheet5.Select
Dim rowselect As Double
rowselect = Me.cmbiqama.Value
rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxcountry.Value
Cells(rowselect, 7) = Me.ComboBox2.Value
Cells(rowselect, 7) = Me.textBASICSALARY.Value
Cells(rowselect, 9) = Me.textFOOD.Value
Cells(rowselect, 10) = Me.textTRANS.Value
Cells(rowselect, 11) = Me.textOTHERS.Value
Cells(rowselect, 8) = Me.textOVERTIME.Value
Cells(rowselect, 13) = Me.txtab.Value
Cells(rowselect, 14) = Me.txtlate.Value
Cells(rowselect, 15) = Me.txtloan.Value
Cells(rowselect, 16) = Me.txtpenalty.Value
Cells(rowselect, 17) = Me.lbldeduc.Caption
Cells(rowselect, 19) = Me.lblGROSSPAY.Caption
Cells(rowselect, 20) = Me.lblnetpay.Caption
Cells(rowselect, 24) = Me.ComboBox2.Value
End Sub


This is the update code. It is only updating Sheet5.

It still has the error I pointed out in post 21.

[SamT]

Employee Data General Business Rules:
Data must be archived forever
Employee numbers are never reused

Therefore using Row numbers for employee numbers will not work at all times.


Dim Found as Range
Set Found = Sheet5.Range("B:B").Find(Textempnum)
If not Found is nothing then
rowselect = Found.Row
Else
RowSelect = Sheet5.Cells(Rows.Count, "B").End(xlUp).Row + 1
End if

Rows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxcoRows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxco.........................

[Aihmar]

Employee Data General Business Rules:
Data must be archived forever
Employee numbers are never reused

Therefore using Row numbers for employee numbers will not work at all times.


Dim Found as Range
Set Found = Sheet5.Range("B:B").Find(Textempnum)
If not Found is nothing then
rowselect = Found.Row
Else
RowSelect = Sheet5.Cells(Rows.Count, "B").End(xlUp).Row + 1
End if

Rows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxcoRows(rowselect).Select
Cells(rowselect, 2) = Me.Textempnum.Value
Cells(rowselect, 3) = Me.txtiqama.Value
Cells(rowselect, 4) = Me.txtemployeename
Cells(rowselect, 5) = Me.ComboBox1.Value
Cells(rowselect, 6) = Me.cmbxco.........................





Thank you very very much!!! :friends::friends::friends::bow::bow::bow::bow::bow::bow::bow::joy::joy::jo y::joy::2jump::2jump::2jump::2jump:

it worked perfectly. thank you so so much

[Aihmar]

its possible to print data grid list box?

[Aihmar]

https://www.dropbox.com/s/pdxd9im22qw7u6b/Screenshot%202018-02-03%2014.14.41.png?dl=0


picture of my data grid

[Aihmar]

21498

1,048,559 rows for employees? That's quite a big company.

I've shrunk your table to fit the data you have in it leaving a few columns for future.

I've taken out the 2 Global Dim's that are causing confusion.

I've removed the picture, pretty as it was, from sheet 4 to make the file size fit here. You can put that back in by all means.

I've taken out the DoEvents which was superfluous.

The form will open now, but i couldn't view all of it because of its size.




NOT RESPONDING AGAIN PLEASE HELP PAULKED

THANK YOU VERY MUCH21533

[SamT]

Are you satisfied with the solution?

Do you need more help?

[Aihmar]

Are you satisfied with the solution?

Do you need more help?

Yes, i am very satisfied. thank you very to you all.


Yes, I do need more help in VBA excel, I'm still learning excel
can you help me? please

thank you very much in advance.


because of you guys in learning many thanks to you all, no words can express.