Macro Adjustment Needed To Stop Deleting Row When Cancelled

[JimmiOO]

Hi

I have a Macro that brings up a a UserForm when a cell is clicked, it works well but whenever the Row & Column header is clicked and it highlights the entire row / column the UserForm comes up, that is ok but if i click cancel it deletes everything in the entire row / column. Is there away to fix this so that the userform only comes up when the actual cell is clicked? or it does not delete anything when cancelled? I have added the macro below.

Thanks

Please Login or Register  to view this content.

The userform code

Please Login or Register  to view this content.

[JimmiOO]

Anyone know how to sort this problem?

[JimmiOO]

Anyone shed some light on this?

[TMS]

It would be easier to work on the problem if you uploaded a dummy workbook with some typical data and an outline of what the process should do.

At the moment, anyone thinking of helping will need to guess what the form looks like and what you're trying to do.

Regards

[shg]

Maybe like this:

Please Login or Register  to view this content.

Please change the QUOTE tags in your first post to CODE tags.

[JimmiOO]

Hi shg

I have tried your code and i am getting a run time error if i click anywhere in the row other than the cell that brings the userform up. when i do a debug i it is highlighting thgis bit of code

Please Login or Register  to view this content.

[JimmiOO]

Hi shg

I have tried your code and i am getting a run time error if i click anywhere in the row other than the cell that brings the userform up. when i do a debug i it is highlighting thgis bit of code

Please Login or Register  to view this content.

I have to click in other cells in the row before i use the userform

Thanks for the help though, very much appreciated

[JimmiOO]

I have added a sample sheet to show you what i mean.

On the sample if you click on the box all is good, If you click on the J column the box appears if you cancel it fills the column with text, if you click on the I column the box appears and when you cancell it delets the entire column.

Can this be fixed? I don,t mind if the box comes up when the column is clicked as long as when you click cancel it leaves everything the way it was

[JimmiOO]

Tried a few things but not having much luck with this at all.

[shg]

I still don't understand what the code is supposed to do, but my suggestion has an error. Change to

Please Login or Register  to view this content.

[JimmiOO]

Hi shg

If you open the sample and click on any the boxes at the top a userform will appear and you just enter a number that relates to spreadsheet and a date, its just a quick way to insert theinfo thats needed in them boxes. The problem i am getting is when i highlight any row on the top the userform comes up, when i click cancel it deletes all the data in the entire row. I still need the userform to appear when any the boxes either I2 , L2, O2, R2, U2, X2, AA2, AD2 is clicked.

I have tried your updated cade by now the user form does not come up when i click in box, in the sample it is I2

[MarvinP]

Perhaps the control properties on the userform have a rowsource specified that is writing back nothing when you exit the userform? The code wouldn't show the problem. It would be the settings of properties on the userform.

[JimmiOO]

Perhaps the control properties on the userform have a rowsource specified that is writing back nothing when you exit the userform? The code wouldn't show the problem. It would be the settings of properties on the userform.

I don't see anything unusual in the properties?

[MarvinP]

Do you know how to set a breakpoint and step through your code? This is a great way to solve your problem.

[JimmiOO]

Hi Marvin i don't yet, could you explain and i will give it a go?

[MarvinP]

View your code and click on the margin next to the first statement past the Dim statements. For you it would be the screenupdating... A big red dot appears. This tells the code to stop when it hits this point. You then run your code, do whatever you do to get it running. All of a sudden the code stops and a yellow highlight shows on the line you specified. You then F8 or step through the code one line at a time and see what it does. You can hover over variables and it will tell you what value they are.
You should be able to step through your code and see which statement deletes that row/column and fix it.

You can set multiple breakpoints in your code. You should set a breakpoint also in your Userform Code to see if the problem is happening there also.

You'll be a VBA guru in no time!

[romperstomper]

Add:

Please Login or Register  to view this content.

to the top of the sub.

[MarvinP]

Here is a link about Debugging VBA Code
I use the Immediate window a lot to see if my syntax is correct and step through code all the time to see why things don't work.

[JimmiOO]

View your code and click on the margin next to the first statement past the Dim statements. For you it would be the screenupdating... A big red dot appears. This tells the code to stop when it hits this point. You then run your code, do whatever you do to get it running. All of a sudden the code stops and a yellow highlight shows on the line you specified. You then F8 or step through the code one line at a time and see what it does. You can hover over variables and it will tell you what value they are.
You should be able to step through your code and see which statement deletes that row/column and fix it.

You can set multiple breakpoints in your code. You should set a breakpoint also in your Userform Code to see if the problem is happening there also.

You'll be a VBA guru in no time!

Marvin thanks very much i have went through the code and this will come in very very handy, however the problem i have hear is the code is working fine and doing what i want it to do, it is only when i highlight the column then click cancel the error occurs, i am not sure how i would replicate that to see whats happening.

[JimmiOO]

Add:

Please Login or Register  to view this content.

to the top of the sub.

Romperstomper that has fixed the problem, thanks you very much, can you tell me how you managed to see the problem so i can try myself to see what was going wrong?

Thanks for all the help everyone

[romperstomper]

You said you didn't want the form popping up if you selected an entire column, so I just added a test so that if the selection had as many rows as there are rows on the sheet, the sub stopped.

[JimmiOO]

You said you didn't want the form popping up if you selected an entire column, so I just added a test so that if the selection had as many rows as there are rows on the sheet, the sub stopped.

Thank you, i have learned quite a lot on this, Problem Solved.