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

[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.

[paulked]

Zip the file, then try

[Aihmar]

i zip the file still exceeds the limit

[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/z4vbtzb4ly...roll.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]

jouna payroll-Ked.xlsm

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