PDA

View Full Version : Passing Data from UserForm to Sheet makes excel to crash (Why?)



10shlomi10
12-20-2016, 02:10 AM
Good Morning ,

Have a strange thing....

Maybe it a silly thing but I couldn't figure out what cause excel to crash (meaning, close and reopened) .
when I press the button on UserForm to pass the data from UserForm Controls to worksheet, excel crash:(

I see that he crashes when he arrived to a rather simple line of code which is to transfer the TextBox text, from UserForm (called UpdateIndex), into the sheet on last empty row.
for some unknown reason excel then crash.



'To find LastRow I'm using the line:
LastRow = WorksheetFunction.CountA(Sheets("AccIndex").Range("A:A"))

'Last Empty Row:
LastEmptyRow = LastRow + 1

If UpdateIndex.TextBox1 = vbNullString Then Exit Sub
Sheets("AccIndex").Cells(LastEmptyRow, 1) = CStr(UpdateIndex.TextBox1)


If UpdateIndex.TextBox2 = vbNullString Then Exit Sub
Sheets("AccIndex").Cells(LastEmptyRow, 2) = CStr(UpdateIndex.TextBox2)


If UpdateIndex.ComboBox1 = vbNullString Then GoTo MyMsg
Sheets("AccIndex").Cells(LastEmptyRow, 3) = CStr(UpdateIndex.ComboBox1)





There are more ComboBoxes (10 of those) but it's keep the same principal as show above.

The more un-logic thing is when I pass the data to LastRow it work prefect. :wot
of course, no can do as it rewrite on data I already have in that cell.

The Data on sheet is TABLE (not regular range, so , hopefully , it will resize in every new row.
I've tried the above on regular range as well, and it acts same, i.e: crashes:banghead:

Run it now and it gives me the following error:
1787517876

as the file contain some other UserForms and code I can not load it to forum.
(I can send it in private)

Can please someone help me with that ?:(

All The Best !

Bob Phillips
12-20-2016, 07:35 AM
Can you post the workbook so that we can try it?

10shlomi10
12-20-2016, 07:56 AM
I can send it personaly.....
What's your email?

Bob Phillips
12-20-2016, 08:03 AM
No no, post it here as an attachment, others can see it then.

10shlomi10
12-21-2016, 12:29 AM
O.K....
Here it is.

The logic of the file:
1. When open the file you should first refresh Data. The code to connect to DATA BASE Cube is not installed in the attached file. but it checks another think. so....press the big blue button.
2. you'll get two Msgboxs. 1. which will inform you that are some accounts found without Index Name and it will ask you if you wish to correct those now. Press YES.
3. A UserForm will be open with unique account number list (came from DATA sheet), which represent all cells from DATA sheet with #N/A on column M. in attached file you'll see just 1 item (for example, but there can be a lot more....)
4. double click this item. Another UserFrom will pop up. on this UserForm you set the category names of the Index account number .
5. after choosing the different categories Press >> "Pass data to index" >> IN THIS STAGE THE EXCEL CRASH !:wot

The second thing I want to do and couldn't find the right way is....
After I update the Account Index Name (Press >> "Pass data to index") I want the item on ListBox, which showed the item with N/A, will disappear as item has no #N/A any more.

Thanks !!: pray2: