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

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

Dave, thanks for the info. I knew about the nul. And you did answer my
question. Yes colum d contains formula =proper(c1). I just figured that
is nothing was in the cell then there was nothing. Forgot about the
formula. I have just started learning this stuff and there is soooo much
to learn.


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