PDA

View Full Version : my excel vba stop running when i run the userform and not responding



Aihmar
01-30-2018, 10:18 PM
hi

Good Day!

i dont know whats wrong with my excel codes.

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

paulked
01-30-2018, 10:46 PM
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
01-30-2018, 11:06 PM
#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
01-30-2018, 11:07 PM
what's wrong with my FOR code?

paulked
01-30-2018, 11:42 PM
Remove the DoEvents, that may be causing it to hang

Aihmar
01-30-2018, 11:48 PM
what's wrong with my FOR code?

i have removed the DoEvents still the userform1 still not showing

paulked
01-30-2018, 11:57 PM
1. Did it stop the hanging?

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

Aihmar
01-31-2018, 12:04 AM
1. it worsen excel now not responding

2. when i press run or F5 excel not responding

Aihmar
01-31-2018, 12:05 AM
i cannot upload the file because it is larger than 1MB. 1.2MB to be exact.:crying:

paulked
01-31-2018, 12:18 AM
Zip the file, then try

Aihmar
01-31-2018, 01:05 AM
i zip the file still exceeds the limit:crying::crying::help

paulked
01-31-2018, 01:12 AM
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
01-31-2018, 05:38 AM
https://www.dropbox.com/s/z4vbtzb4ly2kv9m/jouna%20payroll.xlsm?dl=0 .this is the link to the file

paulked
01-31-2018, 07:23 AM
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
01-31-2018, 07:59 AM
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
01-31-2018, 01:20 PM
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
01-31-2018, 01:24 PM
where is my mistake in the codings?
thank you very much. you saved me .

SamT
01-31-2018, 03:13 PM
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
01-31-2018, 08:44 PM
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
02-01-2018, 01:52 AM
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
02-01-2018, 02:14 AM
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
02-01-2018, 02:30 AM
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
02-01-2018, 03:13 AM
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
02-01-2018, 03:55 AM
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
02-01-2018, 04:07 AM
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
02-01-2018, 04:10 AM
i have uploaded the filrfor reference : pray2:: pray2:: pray2:: pray2:

paulked
02-01-2018, 05:41 AM
21507

It worked for me!

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

Aihmar
02-01-2018, 07:47 AM
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
02-01-2018, 08:02 AM
21509

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

Aihmar
02-01-2018, 08:17 AM
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
02-01-2018, 08:29 AM
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
02-01-2018, 09:41 AM
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
02-02-2018, 11:26 PM
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
02-03-2018, 04:13 AM
its possible to print data grid list box?

Aihmar
02-03-2018, 04:16 AM
https://www.dropbox.com/s/pdxd9im22qw7u6b/Screenshot%202018-02-03%2014.14.41.png?dl=0


picture of my data grid

Aihmar
02-04-2018, 05:30 AM
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
02-04-2018, 10:42 AM
Are you satisfied with the solution?

Do you need more help?

Aihmar
02-04-2018, 12:25 PM
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.