Command button - check listbox contents before activating?

[Excel 2003]

I have command buttons on the userform which access other userforms (which 
drive the spreadsheet data entry)

I have a listbox on the userform displaying a number.

I would like to enter code into the command button activations such that if 
the number in the listbox does not equal the  number stored within the code 
of the command button then a mesage box is displayed and the command button 
does not activate its sub routines.

Can anyone help?

Thanks,

Roger
0
Utf
12/3/2009 5:00:02 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
962 Views

Similar Articles

[PageSpeed] 26

Something like this:

Option Explicit

Public Property Get MayProceed() As Boolean

    Const MySecretNumber As Long =3D 7

    With Me.ListBox1
        If Not .Value =3D MySecretNumber Or .ListIndex =3D -1 Then
            MayProceed =3D False
        Else
            MayProceed =3D True
        End If
    End With

End Property


Private Sub CommandButton1_Click()

    If Not MayProceed Then
        MsgBox "Uups - numbers don't match!!", vbExclamation
        Exit Sub
    End If

End Sub


On 3 Dec, 17:00, Roger on Excel
<RogeronEx...@discussions.microsoft.com> wrote:
> [Excel 2003]
>
> I have command buttons on the userform which access other userforms (whic=
h
> drive the spreadsheet data entry)
>
> I have a listbox on the userform displaying a number.
>
> I would like to enter code into the command button activations such that =
if
> the number in the listbox does not equal the =A0number stored within the =
code
> of the command button then a mesage box is displayed and the command butt=
on
> does not activate its sub routines.
>
> Can anyone help?
>
> Thanks,
>
> Roger

0
AB
12/3/2009 5:40:33 PM
Hi,

Thanks for the code.  Would you know how to make it work if the listbox 
contents are a text string?

Roger

"AB" wrote:

> Something like this:
> 
> Option Explicit
> 
> Public Property Get MayProceed() As Boolean
> 
>     Const MySecretNumber As Long = 7
> 
>     With Me.ListBox1
>         If Not .Value = MySecretNumber Or .ListIndex = -1 Then
>             MayProceed = False
>         Else
>             MayProceed = True
>         End If
>     End With
> 
> End Property
> 
> 
> Private Sub CommandButton1_Click()
> 
>     If Not MayProceed Then
>         MsgBox "Uups - numbers don't match!!", vbExclamation
>         Exit Sub
>     End If
> 
> End Sub
> 
> 
> On 3 Dec, 17:00, Roger on Excel
> <RogeronEx...@discussions.microsoft.com> wrote:
> > [Excel 2003]
> >
> > I have command buttons on the userform which access other userforms (which
> > drive the spreadsheet data entry)
> >
> > I have a listbox on the userform displaying a number.
> >
> > I would like to enter code into the command button activations such that if
> > the number in the listbox does not equal the  number stored within the code
> > of the command button then a mesage box is displayed and the command button
> > does not activate its sub routines.
> >
> > Can anyone help?
> >
> > Thanks,
> >
> > Roger
> 
> .
> 
0
Utf
12/4/2009 12:40:02 AM
Hi,

if the only difference is comparing strings instead of comparing
numbers then technically the only change in the code necessary would
be to dim the constant with a different type (string instead of Long)
like this:

replace this:
    Const MySecretNumber As Long =3D 7

with this
    Const MySecretNumber As String =3D "7"

Obviously the "7" can be anything you need (like "Seven" or "Apple"
or ...)

Post back if i misunderstood the question or didn't answer it.



On Dec 4, 12:40=A0am, Roger on Excel
<RogeronEx...@discussions.microsoft.com> wrote:
> Hi,
>
> Thanks for the code. =A0Would you know how to make it work if the listbox
> contents are a text string?
>
> Roger
>
>
>
> "AB" wrote:
> > Something like this:
>
> > Option Explicit
>
> > Public Property Get MayProceed() As Boolean
>
> > =A0 =A0 Const MySecretNumber As Long =3D 7
>
> > =A0 =A0 With Me.ListBox1
> > =A0 =A0 =A0 =A0 If Not .Value =3D MySecretNumber Or .ListIndex =3D -1 T=
hen
> > =A0 =A0 =A0 =A0 =A0 =A0 MayProceed =3D False
> > =A0 =A0 =A0 =A0 Else
> > =A0 =A0 =A0 =A0 =A0 =A0 MayProceed =3D True
> > =A0 =A0 =A0 =A0 End If
> > =A0 =A0 End With
>
> > End Property
>
> > Private Sub CommandButton1_Click()
>
> > =A0 =A0 If Not MayProceed Then
> > =A0 =A0 =A0 =A0 MsgBox "Uups - numbers don't match!!", vbExclamation
> > =A0 =A0 =A0 =A0 Exit Sub
> > =A0 =A0 End If
>
> > End Sub
>
> > On 3 Dec, 17:00, Roger on Excel
> > <RogeronEx...@discussions.microsoft.com> wrote:
> > > [Excel 2003]
>
> > > I have command buttons on the userform which access other userforms (=
which
> > > drive the spreadsheet data entry)
>
> > > I have a listbox on the userform displaying a number.
>
> > > I would like to enter code into the command button activations such t=
hat if
> > > the number in the listbox does not equal the =A0number stored within =
the code
> > > of the command button then a mesage box is displayed and the command =
button
> > > does not activate its sub routines.
>
> > > Can anyone help?
>
> > > Thanks,
>
> > > Roger
>
> > .- Hide quoted text -
>
> - Show quoted text -

0
AB
12/4/2009 9:00:23 AM
Reply:

Similar Artilces:

part of procedure not executed by button
Hi all, In the NG I saw earlier questions about residual "down arrows", I mean that the filter arrows in the top row will not (all) disappear after finishing the filtering. Saw no usefull answer in the NG. Maybe it occurs when Excel (I use Excel 2k with XP) gets "overloaded" because the spreadsheet contains very much formulas, connections to other complex sheets or workbooks with graphs (or should I say charts?) etc. Perhaps an area of memory gets clotted with all kinds of scribblings, like with exhausting the memory space for system resources under Windows 3.11. When reac...

Inactivate and re-activate a user: now the user cannot login...
Hi All, for some testing I have inactivated some users... now I need to re-activate the same users but it seems that they cannot login into PWA anymore... I have tried to give them administration permission, but nothing... it seems that the status is still inactive (also if I have change the status to active)... when I try to login with those user I get prompted the insert User and password 3 times and the result is the following: HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS) any suggestion??? should...

check e-mail on startup of outlook 2003
I hope somebody can help me, I know it's possible to do this but i can't remember how i managed to get it to work. I'm on a lan connection to the internet that is alwasy on. I want outlook 2003 to check for new e-mails every time I open the program as opposed to me opening the program and then me hitting send/receive. How can I get it to check automatically? Thanks. Setting any automatic polling interval in Tools > Send/Receive... will also force a poll on launch. -- Russ Valentine [MVP-Outlook] "Evan" <hardtarget83@yahoo.ca> wrote in message news:7663...

Edit, Delete command buttons next to each record
Hi there, I have a form, with a subform showing records from a table. Would it be possible to put command buttons next to each record, without having to hard-code a set number of cmd buttons, and showing on scrollable data within this subform? Furthermore, when clicking the Edit cmd button, it should open another form and populate this with the selected record... how can I achieve this? Thank you kindly! -- The Psyber Fox http://www.psyberconsulting.co.za ...

Problem with Active X Control
Sir, I am integrating flash with VC++. I am using Dialog Based App. I inserted this Active X Control. I wanted to show both this Control and Dialog Window in a maximized state. I called ShowWindow(SW_MAXIMIZED) in OnInitDialog. When I execute the App I get the main window in maximized but the control in that is not at all maximized. Pls help. Regards Chintu YKUT wrote: > Sir, > > I am integrating flash with VC++. > > I am using Dialog Based App. > > I inserted this Active X Control. > > I wanted to show both this Control and Dialog Window in a maximized ...

Spell Check not Working
I just installed Microsoft Publisher 2007. All is well except my spell check is not working now. I went into settings and it looks like it's working but when I type in test words, it says they are right. This is effecting Microsoft Outlook and Word as well. I am running Windows Vista. I'd appreciate any advice! Thanks! From Office Newsgroup a. Close Publisher and any other open applications. b. Click on Start | Run | Open: regedit . c. If the key "HKEY_CURRENT_USER\Software\Microsoft\Shared Tools\Proofing Tools\1.0\Override" exists, delete it. d. Exit regedit. e. Re...

Spell Check Help
Good Day, I have this code in my text box that accepts text (memo): If Len(Me!CASEFACTS & "") > 0 Then DoCmd.RunCommand acCmdSpelling Else Exit Sub End If The only problem I'm having with spell check is that it checks the whole form. I just want to spell check only the textbox that the code is in. Is this possible? bladelock, That is an issue, so I use the one below. I added the line * If ctlSpell.BackColor = 15400959 Then* so that any box on a form I want spell checked I tint that color and it only checks that one. Us...

Incorrect command line parameters
I'm trying to install CRM Sales for Outlook on my machine, but when running the setup I get this popup message from Windows Installer: "Incorrect command line parameters.", and then nothing more except some info about Windows Installer and an OK-button... The machine (virtual, run on VMware) have OS Win2000pro, SP3, and I have installed MS Office 2000 SR1, SP3 and the Hotfix 228512 required, Also have updated to IE 6.0 Anybody with experience of this problem or hints on a solution? /Mattias ...

Active X text box control
I want to put a text box in a cell. I have followed all the instructions I can find and it still isn't working. I try to type in the box and then go to another cell and the text shows up in the cell behind the text box. What am I doing wrong? The Active X textbox control comes from the control toolbox. To adjust it's settings you need to put the sheet into Design mode by clicking the first button on that toolbar (looks like a triangle, pencil and ruler). Right click the text box and select Properties. Add a cell address to the Linked Cell and then Exit Desing mode. T...

Activating a Chart object
Hi, Whats the correct syntax to access (activate) a chart within a chart sheet in an excel workbook? I tried Sheets("Chart1").Chartobjects("Chart 5").activate Also, Charts("Chart1").Chartobjects("Chart 5").activate But both of them gave run-time error. Basically I want to select a single chart within a chart sheet and change the Auto-Scale property to False. (I need to ultimately do this within all Excel Chart OLE's within a PPT, so my syntaxes actually have the name of the PPT session along with object name appended to them.) Regards, H...

options/ Voting / Radio buttons counting
How do I count voting buttons ? Im sending an questionaire via email and have the need for rapidly counting the responses. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements You could make sure each checkbox has an associated linked cell. Say your linked cells were in A1:A10, you could use: =countif(a1:a10,true) to find the number of checked checkboxes. ...

I have done something to make my email send button disappear
I can't send emails in my outlook 2003. I must have changed something to make the send button disappear. It used to have a drop down menu that would let me choose which email account to send mail from. Stephen Oatway <Stephen Oatway@discussions.microsoft.com> wrote: > I can't send emails in my outlook 2003. I must have changed something > to make the send button disappear. It used to have a drop down menu > that would let me choose which email account to send mail from. Sounds like Outlook doesn't see your accounts. Create a new mail profile with Control Panel...

Using english and non-English Excel commands simultaneously in a non-English installation
Hi community, I like this forum very much - I got many valuable insights from it. In the past I had an English Excel installation at the office available, recently my employer moved to the German version and deinstalled the english one. Now, when i use a english command like =VLOOKUP(A1,'Sheet 2'! A1:H200,8,FALSE) adopting the country settings for "," with ";" Excel comes up with #NAME? since I used the english term, not the german one - but sometimes the german equivalent is not at hand as fast as needed ;-) However if a open a english coined workbook o...

undo
It happened all of a sudden: when I type something in a cell and move to another cell, the undo button greys and the undo function is therefore disabled. It never happened before, and I have no recollection of having modified settings. in this regard I could not find any setting to enable/disable the undo-redo buttons. can please somebody help/ thanks Have you got some macro running? Mostropovich wrote: > It happened all of a sudden: > when I type something in a cell and move to another cell, the undo button > greys and the undo function is therefore disabled. > It never hap...

Check box continue
Forgot to attach this information to mail about Check Boxes. Policy Name Policy Contract # Monthly Premium Y/N «Policy_Name» «Policy_» «Amount» «Policy_Name2» «Policy_2» «Amount2» «Policy_Name3» «Policy_3» «Amount3» «Policy_Name4» «Policy_4» «Amount4» Can you give us a bit more information on the while process/what it is that you want to do? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consu...

Office 2010 beta Product Key Activation
I have installed office 2010 a couple of weeks ago. It worked fine so far. For a couple days now, whenever opening an office program (excel, word, outlook, etc.), the program keeps asking me for the product key, which I inserted already during the initial installation process. When following the instructions to change the product key, the program starts a re-installation rather than just verifying the product key and finishing the activation process. Thanks for any help ! Here is the Office 2010 forum http://social.technet.microsoft.com/Forums/en/category/office2010 hcn ...

Outlook Checking Forcing sequential check of multiple pop accounts
I've been bouncing back and forth between outlook and outlook express for quite a while now and I'd use outlook primarily except I'm getting frustrated with how it handles send/receive for multiple POP accounts. If I perform a general send and receive, even after semi limiting the accounts through groups, the downloading of messages takes a long time. I often run into problems where my connection to one of my servers is reset and the ever dreaded problem of duplicate messages. If I do a send/receive on a mailbox by mailbox basis, the downloads are quick and I never seem to run in...

VC6 / VC7 Issue
My program uses VC6 SP5 with MFC. I have been experiencing "Access denied" problems on a remote network when a user saves an existing file from a W2000 workstation to a W2000 server that has Active Directory installed. I have found that I can overcome the problem by overriding the CDocument::GetFile() function and using the version of CMirrorFile::Open() from VC7. I have not been able to determine why the VC7 code works while the VC6 does not. Any suggestions? Both VC6 and VC7 use call to the superseded GetDiskFreeSpace() rather than GetDiskFreeSpaceEx() when checking for the amount...

Transparent Background on Radio Buttons
<second attempt to post> I have several radio buttons on my form that are within a table cell with a colored background. I went into Design Mode and looked the properties of my radio buttons. I set the BackStyle value to "0 - fmBackStyleTransparent" and while in Design Mode everything looks fine. When I click out of Design Mode, the white background around the radio buttons reappears! The form prints okay, but some users will fill out the form electronically. I don't want to leave the file in Design mode (in fact, I'd like to protect it) but I ne...

Distributing Activities
I am working from within the Outlook client and trying to Distribute a Campaign Activity to the Account Owner. In this instance it is a telephone follow up to a mailshot. Having clicked Distribute Activity, filled in the Subject line, selected the owning team, clicked Distribute and then selected the Phone Calls Owner to be "The owners of the records in the target marketing lists" I then get an error message saying "You do not have enough privileges to access the Microsoft CRM object or perform the requested operation". Given that my role is set as a System Administr...

Cell contents not appearing in title bar
When I highlight a cell the contents don't appear in the line at the top so that I can edit them Hi maybe the sheet is protected? >-----Original Message----- >When I highlight a cell the contents don't appear in the line at the top so >that I can edit them >. > The cell contents are not supposed to appear in the Title bar. They appear in the Formula bar. To display the formula bar, click on View | Formula Bar. "petersmyth1" wrote: > When I highlight a cell the contents don't appear in the line at the top so > that I can edit them If your...

Active Desktop & Hyperlinks in visio
I have documented a cross functional business process with hyperlinks to documents on our network. There's 3 pages in this flowchart. Everything works well in a separate IE process but if I set Active desktop to the first page no problem. If I click on any links, I get an error. In visio 03, I can't remove the relative hyperlink to change it to an absolute link (I did that in MindManager X5 and it solved the problem). Is there a solution ? (using XP pro SP2 in the office) ...

No send button for emails from Windows Picture and Fax Viewer
I am trying to send photos from Windows Picture and Fax Viewer, but there is no send button. Help! Right click on the image and choose Send To-> Mail Recipient -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Deb in OR" <Deb in OR@discussions.microsoft.com> wrote in message news:AE87D89F-65E0-4A79-8E68-F27BB8481CB5@microsoft.com... > I am trying to send photos from W...

Option Button as Query Sort Parameter
Is it possible to use an option button as a query sort option? I have a form with two option buttons... when one option button is clicked I want my query to be sorted according to that... any ideas? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200710/1 Use a calculated field in your query --- SortOrder: IIF([Forms]![FormName]![Frame1] = 1, [Field_X], [Field_Y]) Nest more IIF's for more sorts. -- KARL DEWEY Build a little - Test a little "Barkley via AccessMonster.com" wrote: > Is it possible to us...

Check the SubItems of the ListControl
hi all, i want to check the Items in the ListControl SubItems, but by default the ListControl First Item gives checked. any method to check the subitems also. thanks, uday. Out of the box, there is no way to add check/uncheck functionality to subitems, but by using ownerdarw it is possible to do so. G Himangi, Sky Software http://www.ssware.com *Shell MegaPack for ActiveX & .Net : Advanced Controls for Drop-In Windows Explorer GUI for your App. *EZNamespaceExtensions.Net : Rapid Development of Shell Namespace Extensions in .Net *EZShellExtensions.Net : Rapid Development of IE Toolb...