I built a small userform with a combobox and two buttons. I added some test
data and ran your code.
It worked ok.
Since you never dimmed Nul, it's a variant that has never been initialized (it's
empty).
But both the assignment to "" and nul did the same thing at the worksheet view.
So I don't have an answer why activecell.value = "" didn't work.
But your data in D4:d33 may not be empty cells. If they were formulas that
evaluated to "" and then converted to values, then these cells aren't really
empty.
Try =isblank(d4) in a helper cell (assumes d4 looks empty).
You could just skim through the range and hit the delete key to actually change
them to empty.
Pete wrote:
I have a user form that when a user right clicks on a cell a form pops up
and lets them select vendors from a drop down list. The vendors are
stored on a separate worksheet and does contain blanks. Everything works
fine except that if the user selects a blank from the list it some how is
not really blank. The reason I know this is that when I sort the list.
The blanks show at the top of the sorted list. I figured I could check
for "" and then set the cell to "" but that did not work. So just for the
heck of it I set the cell to NUL and it worked. My question is why won't
"" not work and NUL does work?
Private Sub Cancel_Click()
Unload Me
End Sub
Private Sub OkSubmit_Click()
If Vendor = "" Then
ActiveCell.Value = "" 'this does not work. Why?
ActiveCell.Value = nul 'but this does accomplish it. Why?
Else
ActiveCell.Value = UCase(Vendor)
End If
Unload Me
End Sub
Sub UserForm_Initialize()
'Selects info from Vendor List and puts in drop down box
Vendor.List = Sheets("Vendor List").Range("D4:D33").Value
'if the active cell has nothing then select the first
'item from the vendor list
'else take the active cell and select it in upper case
If ActiveCell = "" Then
Vendor = Sheets("Vendor List").Range("D4")
Else
Vendor = UCase(ActiveCell)
End If
End Sub
--
Dave Peterson