PDA

View Full Version : Solved: clear cells without form appearing



ndendrinos
08-13-2009, 06:39 PM
Please look at the attachment ,I have instructions that illustrate the problem. Thank you

D_Rennie
08-13-2009, 10:07 PM
Im not to sure what you are trying to achive.
It seams that you have a few things working agenst you.
if you rem out 'If WorksheetFunction.CountIf(Range("B2:B10"), Target.Value) > 1 Then
'MsgBox "Duplicate choice ... If not a mistake please increase previous selection", vbCritical
'ActiveCell.Offset(0, -1).Resize(, 2).ClearContents
'End If
you will be able to make the selections that you want.
If you want to see it there are duplicate;s of the item that you are trying to insert im sure there would be a better way to go about it.

D_Rennie
08-13-2009, 10:16 PM
Also you dont see why you have "salse catalog" range"c1:e1" setup like that you can find the price value in "sheet1" column c using a formular (im not to good at formulars so hopefully someone else will provide that).

ndendrinos
08-14-2009, 01:40 AM
Hello D_Rennie,
The problem I'm trying to address is :"Clear cells without form appearing"
Thank you

GTO
08-14-2009, 03:08 AM
Dear ndendrinos,

I am afraid it is less clear than you may believe. My guess would be that the example "Test.xls" is too different from the actual project for us to discern what is going on.

What cells are we trying to clear w/o the UF popping up, B2:B10? Presuming yes for the moment, we'd need to have a clearer understanding of the "big picture" to choose something other than the selection change event to display the userform.

I am not feeling terribly gifted at communicating tonight, but hopefully that makes sense.

Mark

D_Rennie
08-14-2009, 03:28 AM
Sorry about that i missed the thread title.

You can disable events while a sub is running.
Sub deletedata()
appication.EnableEvents = False
Range("A:A,B:B").Delete
appication.EnableEvents = True
End Sub


This example will delete the entire contence of row A and B.
Is there any data with these rows apart from row 1 that you would like to keep.

Ill take a look after dinner,

ndendrinos
08-14-2009, 08:47 AM
Got it to work ... see this attachment. (pls feel free to offer a less convoluted code)
Thank you both.

D_Rennie
08-14-2009, 05:35 PM
OK, I would have never got what you where trying to achive from the provided info.
When using VBa the .select procudure is never realy needed so.
ActiveCell.Offset(0, -1).Offset(0, 1).ClearContents
Selection.Offset(-0, -1).ClearContents

The way that you are the worksheet change everytime your userfrom places values into the cells it triggers the change event. to see it working use the debug steep into and keep pressing f8

ndendrinos
08-14-2009, 07:49 PM
Much better than my solution ... I will adopt it
Next posting will be way more difficult ... and more detailed...:yes keep an eye open.
Thanks again.