Big Ask? Loading UserForms and then using the Check Box

I am very new to VBA and am trying to do the following, I have created the 
Userform and check boxes but that is as far as i can go.

When a ceratin a cell e.g A1 goes to = red then a userform will pop up with 
a list of check boxes e.g Nexus, G2, Swift, Crest if  Nexus and Crest check 
box is ticked then the word nexus will appear A2 and Crest in A3. Or if G2 
and Swift were checked then G2 would be in cell A2 and Swift in cell C3.

 Many Thanks
0
Jelinek1 (3)
1/10/2006 10:19:03 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
465 Views

Similar Articles

[PageSpeed] 6

Changing the format of a cell isn't enough to make excel notice the change.

But you could tie showing your userform to a change in a column (or a range of
columns).

If you want to try it, rightclick on the worksheet tab that should have this
behavior and choose view code.  Then paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a1:L1")) Is Nothing Then
        Exit Sub
    End If
    
    UserForm1.Show
    
End Sub



I created a userform (userform1) with a bunch of checkboxes and two
commandbuttons (ok/cancel).  I put the values in the captions of each checkbox.

Option Explicit
Private Sub CommandButton2_Click()
    Unload Me
End Sub
Private Sub CommandButton1_Click()
    Dim iCtr As Long
    Dim ctrl As Control
    
    iCtr = 0
    Application.EnableEvents = False
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.CheckBox Then
            If ctrl.Object.Value = True Then
                iCtr = iCtr + 1
                ActiveCell.Offset(iCtr, 0).Value = ctrl.Object.Caption
            End If
        End If
    Next ctrl
    Application.EnableEvents = True
    
    Unload Me
            
End Sub

Jelinek wrote:
> 
> I am very new to VBA and am trying to do the following, I have created the
> Userform and check boxes but that is as far as i can go.
> 
> When a ceratin a cell e.g A1 goes to = red then a userform will pop up with
> a list of check boxes e.g Nexus, G2, Swift, Crest if  Nexus and Crest check
> box is ticked then the word nexus will appear A2 and Crest in A3. Or if G2
> and Swift were checked then G2 would be in cell A2 and Swift in cell C3.
> 
>  Many Thanks

-- 

Dave Peterson
0
petersod (12004)
1/10/2006 3:42:29 PM
Reply:

Similar Artilces:

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

Excel Drop Down Box
I'm trying to edit an excel worksheet that has drop down boxes. However, the drop down boxes are not typical forms. These drop dow boxes appear to be normal cells (They contain text). When I click o the box, a little gray box shows up w/ a down arrow to the right of th cell. However, if you right click on the cell, there aren't an property options that are displayed. I was wondering if anybody had any idea what kind of drop down box thi is. How can I edit or create one -- Message posted from http://www.ExcelForum.com It sounds like it's under Data|Validation. chris313 wr...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

Help: Budget doesn't load bills for entire year
Budget reports only show the bill from the month it was created August and forward. Since I just upgraded from Quicken, all of the bills were paid before this month. How can I make money realize that the budget should include these as well. ...

Select Names Dialog Box
Could someone help with the following? I am using Access 2003 with outlook 2003 and all I need to do is open outlook and for it to display the Select Names Dialog Box ( it would be nice to open outlook if it was closed) no more than that. Hence I am now after the code to go in the click event of the button. Any examples of VBA code that will work within Access 2003 would be appreciated The Select Names dialog box is programmable starting from Outlook 2007. You can't display it in Outlook 2003. http://msdn.microsoft.com/en-us/library/bb176400.aspx --JP On Feb 5, 3:52=...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

Data Validation Allow List
Is there a way to click on the resulting combo box and have ALL the list items show up instead of just what seems like the default of about 8--I can scroll, but would like to just point and choose? Also, is there a way to change the font of this combo box? The resulting look like they are about 6 or 8 points. Any help is appreciated. Is this the right forum doug? If you are using a combo box (from the control toolbox) as opposed to a validation list you can change visible rows but not if you use validation Debra has a solution here http://www.contextures.com/xlDataVal10.html -- R...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

How to create a scrollable dialog box ?
Hi I've created a dialog resource (it uses CDialog as the base class) that is longer than the height on the screen (this is for WinCE, but the question still applies to desktop windows). I want the user to be able to scroll down the dialog filling in data in editbox's as they go. How do I get the dialog to be scrollable (i.e. a scroll bar at the right hand side that works as a CScrollView would). In eVC++ (and in VC6 also) there is a box to tick of a vertical scrollbar (in the resource editor) but the scroll bar seems to be disabled at run time. Are there some window messages that ...

Front end server with mail boxes??? can it be done #2
sorry for the late post... i have 50 users "joel" <joelbueno@verizon.net> wrote in news:ehG8e.8669$c93.2155@trnddc08: > sorry for the late post... i have 50 users Why? Are you going to have back-end servers? Regards, -- Arlo Clizer Exchange MVP FAQ: http://www.exchangefaq.org Archives: http://groups.google.com "joel" <joelbueno@verizon.net> wrote: >sorry for the late post... i have 50 users No. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Newbee needs help Combo Box access 2007
I have a access 2000 project that would all me to create a combo box the added the following code , so the values from the combo box would be stored in the current form . also when typing the code below access 2000 would bring up the code string example when typing me.s it would bring up the value "shipper" from the field list is this feature gone or just turned off on my copy Example of code From Access 2000 Private Sub Combo 40_AfterUpdate(cancel As Integer) me.shipper = me.combo40.Column(0) me.Address = me.combo40.Column(1) error This error occurs whe...

Content Boxes not appearing on list forms (newform.aspx)
Morning, FYI: I have posted this question on the sharepoint msdn forums and got directed to the SBS newsgroup, I asked this same question there and have been directed to here, i would be very greatfull if someone could please help. I have many workstations (95% now) now not displaying text boxes/content boxes within sharepoint (2003 SBS version). Any other site will display forms but not sharepoint. This does not seem to effect all machines and seems to have nothing to do with permissions as i have tried with all types of users. I have tried changing all the security settin...

Mobile using up CPU
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

Calculate Checks Error
I have a client that has a problem when calculating payroll checks. They built the batch and everything was fine. They checked the sheet for errors-which there were none. Then they went to Calculate payroll and the screen came up with No warnings and No errors. But when they hit the OK button they received the message "The checks cannot be calculated, errors were found when building the check." The user then logged in as user sa and was able to calculate checks without the error. Any suggestions on why the user receives the error? Thanks, Jocelyn "Jocelyn&q...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

Specify data for use in FRx pivot tables
How do I specify what data fields appear in an Excel pivot table (or ..cub file) from FRx? I want to export the natural account number into the pivot table to sort on. TIA ...

using $ in a formula
when in a cell and I type: =$C4, what does that mean vs =C4? Also what does =$C$4 mean vs =C$4? what is the significance of the dollar sign? Thanks Hi Robin it changes the addressing from relative to absolute e.g. if i have in cell C1 the formula =A1*B1 and i fill it down to C2 the formula will change to =A2*B2 or if i fill it across to D1 the formula will change to =B1*C1 - this is relative addressing and is the default in excel however sometimes you want to "fix" the cell reference in a formula and this is where the $ come in =A1*$B$1 filled down from C1 to C2 will change ...