Command Button question

[Patrick Simonds]

On my UserForm I have a ListBox (ListBox1) and 2 Command Buttons (Edit Name,
Delete Name). When I click on one of the two command buttons it runs code to
go to the source for the ListBox and selects the name and then allows you to
Edit or Delete the name.

What I need is a piece of code which will prevent the code associated to
either Command Button from running if no name is selected in ListBox1


I tried:

EditName ()

If ListBox1.Value = "" Then GoTo BlankList

My Code here

BlankList:

End Sub

 

[Nigel]

You could use the listbox events to control the commandbuttons enabled
status, rather than let the button be pressed only to do nothing!

something like....

sub listbox1_change
if listbox1.listindex >= 0 then
commandbutton1.enabled = true
else
commandbuton1.enabled = false
endif
end sub

Put the commandbuton1.enabled = false in the form initialize event.

 

[Kai Uwe Schmidt]

Hi Patrick,

when no selection is made, ListBox.ListIndex has the value -1 (otherwise, it
has a value from 0 to Listbox.Listcount -1).

Public Sub ChangeRowSource ()
If listbox1.listindex = -1 then
MsgBox "Please Select a name first!" : Exit Sub 'quick and very
dirty.
Else
...
End if
End Sub

Regards,
Kai

 

[Patrick Simonds]

Thanks guy here is the code I ended up with, and it works fine, but, there
always seems to be a but.

The source for ListBox1 is a worksheet which has a list of all employees by
job category separated by a blank row between each category, so of course
this blank space appears in the ListBox (which is good because it provides a
visual separator between job categories. So in addition to what the code
below achieves I also need the Edit_Name and Delete_Name buttons disabled
when the blank space between job categories is selected.

Sub listbox1_change()

If ListBox1.ListIndex = -1 Then

Edit_Name.Enabled = False
Delete_Name.Enabled = False

Else

Edit_Name.Enabled = True
Delete_Name.Enabled = True

End If

End Sub

 

[Tom Ogilvy]

Sub listbox1_change()

If Trim(ListBox1.Value) = "" or Listbox1.ListIndex = -1 Then

Edit_Name.Enabled = False
Delete_Name.Enabled = False

Else

Edit_Name.Enabled = True
Delete_Name.Enabled = True

End If

End Sub

 

[Patrick Simonds]

Thanks, but it does not enable the buttons when you select a name in the
listbox.

 

[Tom Ogilvy]

In contrast, it works fine for me - exactly as intended.

 

[Patrick Simonds]

The problem seems to be that If Trim(ListBox1.Value) = "" does not work
(for me). I even tried putting a text value in the cell and the code. Still
would not work.

 

[thesquirrel]

I have only been doing this for a few months, but I have similar code
that may do the job for you...

I use 1 button instead of 2, I change the button caption depending on
what the user is doing and then on button press doing the action the
caption says it is going to do.

My application is more of a form administration so I don't have to be
around for someone to keep the names of staff members up to date.

The form includes a listbox, a textbox and a command button. When the
form loads, the list box has no selection, the textbox has no text and
the button is disabled.

If the user makes a selection on teh list box, the button caption
changes to 'Remove Name' and removes any text from the textbox.

If the focus is shifted to the textbox, the button caption changes to
'Add Name' and any selection on the list box is removed.

When the button is pressed it checks the value of the buttons caption
to decide what it should do.

Code is below:

'#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
' listbox functions

Private Sub lstManager_Enter()
'this will run each time the list box is entered

'check to see if there is any text in the manager text box
If Not Me.txtManager.Value = "" Then Me.txtManager.Value = ""
Me.btnAddRemoveManger.Enabled = False
Me.btnAddRemoveManger.Caption = "Add/Remove Manager"

End Sub
---------------------------------------------------------------------------------------------------------------------------------
Private Sub lstManager_Change()
'this runs each time the listbox changes selection

'change the caption of the button
Me.btnAddRemoveManger.Caption = "Remove Manager"

'enable the button
Me.btnAddRemoveManger.Enabled = True

End Sub
--------------------------------------------------------------------------------------------------------------------------
Private Sub txtManager_Enter()
'this will run each time the text box is entered

'check to see if there is a selection in the list box
If Me.lstManager.ListIndex > -1 Then Me.lstManager.ListIndex = -1
Me.btnAddRemoveManger.Enabled = False
Me.btnAddRemoveManger.Caption = "Add/Remove Manager"

End Sub
-----------------------------------------------------------------------------------------------------------------
Private Sub txtManager_Change()
'this runs each time the listbox changes selection

'change the caption of the button
Me.btnAddRemoveManger.Caption = "Add Manager"

'enable the button
Me.btnAddRemoveManger.Enabled = True

End Sub
-----------------------------------------------------------------------------------------------------------------
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
' Button Code Below

Private Sub btnAddRemoveManger_Click()

'check to see if we have anything to do. no selection in the list or
no text in box
If Me.lstManager.ListIndex = -1 And Me.txtManager.Value = "" Then
MsgBox "You don't have a manager selected in the list and you
haven't entered a manager name in the textbox.", vbInformation, "I have
nothing to do?!?"
GoTo LastLine
End If

'check to see if we are adding names or removing names
If Me.btnAddRemoveManger.Caption = "Remove Manager" Then

'looks like we are removing a name
'ensure the list contains items
If Me.lstManager.ListCount < 1 Then
'alert the user there is nothing to delete
MsgBox "You don't any Managers to delete, Sorry bro!",
vbExclamation, "No Managers"
Else
If Me.lstManager.ListIndex = -1 Then
MsgBox "You don't have a Manager selected, please make
a selection and try again.", _
vbExclamation, "Make a Selection"
Else
Dim yesno As Byte
yesno = MsgBox("You are about to delete " &
Me.lstManager.Value & " from the Manager list." & vbCrLf & _
"Please be sure this is what you want to do.",
vbYesNo + vbExclamation, "Are you sure?")
Select Case yesno
Case vbYes

'find and remove the item from the list variables
page
Dim MgrRng As Range
Dim Mgr As Range
Set MgrRng = shtVar.Range("F4:F18")

For Each Mgr In MgrRng
If Mgr.Value = Me.lstManager.Value Then
Mgr.Value = ""
Exit For
End If
Next Mgr

'remove the item from the list box
Me.lstManager.RemoveItem (Me.lstManager.ListIndex)

'clear the selection (if any)
Me.lstManager.ListIndex = -1

'disable the button
Me.btnAddRemoveManger.Enabled = False

'rename the button
Me.btnAddRemoveManger.Caption = "Add/Remove
Manager"

Case vbNo
End Select

End If
End If
Else

'adding a name
Set MgrRng = shtVar.Range("F4:F18")

'find the first empty box in the range and insert the new value
For Each Mgr In MgrRng
If IsEmpty(Mgr.Value) Then
Mgr.Value = txtManager.Value
Exit For
End If
Next Mgr

'select the range to sort
shtVar.Range("F4:F18").Select
selection.Sort Key1:=Range("F4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'clear the current list
Me.lstManager.Clear

'rebuild the MGR listbox
For Each Mgr In MgrRng
If Not IsEmpty(Mgr.Value) Then
Debug.Print Mgr.Value
lstManager.AddItem (Mgr.Value)
End If
Next Mgr

'clear the text box
txtManager.Value = ""

'disable the button
Me.btnAddRemoveManger.Enabled = False

'rename the button
Me.btnAddRemoveManger.Caption = "Add/Remove Manager"

End If

LastLine:
End Sub
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
' END OF CODE
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@

If anything else, I hope this gives you some ideas.

theSquirrel

 

[Susan]

in userforms i frequently use command buttons that don't become visible
until some other prequisite is fulfilled.................

xxxxxxxxxxxxxxxxxxxxxx

If Trim(ListBox1.Value) = "" or Listbox1.ListIndex = -1 Then
msgbox "Please Select a name first!"
Exit Sub

Else
Edit_Name.visible=true
Delete_Name.visible=true

End If
End Sub

xxxxxxxxxxxxxxxxxxxxxxxxxx

just another idea nobody mentioned........
susan