Retrieve value from Combo Box and Radio Button

Hi, 

I have two questions about Excel VBA and hope any experts can give me a hint

(1) I wonder how to retrieve automatically the previously saved values from 
the combo box and a group of radio buttons whenever I open the workbook? 

(2) Everytime I need to insert an object, I copy the object to one worksheet 
(named as SLD) from another worksheet (named as Lib) which acts like a 
library. However, because of using the function "SELECT",  I encounter 
"flickering" on the main sheet (named as Sheet1) each time an object is 
inserted. Is there any way that I could eliminate this flickering? Your 
advices are greatly appreciated.  

Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
Sheets("LIB").Select
Select Case myType
        Case 1
            ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select
            Selection.Copy
            If myValue = 1 Then
                Sheets("SLD").Select
                ActiveSheet.Paste
                Selection.Name = "offtrfr1x3wdgoff1"
                Selection.Left = 380
                Selection.Top = 642
            End If
        End Select
Sheet1.Activate

0
Utf
1/7/2010 3:06:06 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
806 Views

Similar Articles

[PageSpeed] 24

1.) Put this code in the Workbook module.  Excel doesn't remember what your 
last value was for your controls, so what you will need to do is store the 
values in cells.  In this example, I stored the value of an option button and 
combobox in the Lib worksheet when the workbook is closed.  You can choose 
whichever event is best in your application.  And you may need to change the 
controls names as well.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Lib").Range("A1").Value = OptionButton1.Value
    Sheets("Lib").Range("A2").Value = ComboBox1.Value
End Sub

Private Sub Workbook_Open()
    MsgBox "Option Button 1 = " & Range("A1").Value
    MsgBox "Combo Box 1= " & Range("A2").Value
End Sub

2.)  To stop the "flickering" just use this:

Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)

   Application.ScreenUpdating = False

   ' your code here

   Application.ScreenUpdating = True

End Sub

Hope this helps!  If so, let me know, click "YES" below.
-- 
Cheers,
Ryan


"newbie" wrote:

> Hi, 
> 
> I have two questions about Excel VBA and hope any experts can give me a hint
> 
> (1) I wonder how to retrieve automatically the previously saved values from 
> the combo box and a group of radio buttons whenever I open the workbook? 
> 
> (2) Everytime I need to insert an object, I copy the object to one worksheet 
> (named as SLD) from another worksheet (named as Lib) which acts like a 
> library. However, because of using the function "SELECT",  I encounter 
> "flickering" on the main sheet (named as Sheet1) each time an object is 
> inserted. Is there any way that I could eliminate this flickering? Your 
> advices are greatly appreciated.  
> 
> Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
> Sheets("LIB").Select
> Select Case myType
>         Case 1
>             ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select
>             Selection.Copy
>             If myValue = 1 Then
>                 Sheets("SLD").Select
>                 ActiveSheet.Paste
>                 Selection.Name = "offtrfr1x3wdgoff1"
>                 Selection.Left = 380
>                 Selection.Top = 642
>             End If
>         End Select
> Sheet1.Activate
> 
0
Utf
1/7/2010 3:58:03 PM
Thanks Very much Ryan. I have finally solved the problems using your code! 
Best wishes, Loy

"Ryan H" wrote:

> 1.) Put this code in the Workbook module.  Excel doesn't remember what your 
> last value was for your controls, so what you will need to do is store the 
> values in cells.  In this example, I stored the value of an option button and 
> combobox in the Lib worksheet when the workbook is closed.  You can choose 
> whichever event is best in your application.  And you may need to change the 
> controls names as well.
> 
> Option Explicit
> 
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>     Sheets("Lib").Range("A1").Value = OptionButton1.Value
>     Sheets("Lib").Range("A2").Value = ComboBox1.Value
> End Sub
> 
> Private Sub Workbook_Open()
>     MsgBox "Option Button 1 = " & Range("A1").Value
>     MsgBox "Combo Box 1= " & Range("A2").Value
> End Sub
> 
> 2.)  To stop the "flickering" just use this:
> 
> Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
> 
>    Application.ScreenUpdating = False
> 
>    ' your code here
> 
>    Application.ScreenUpdating = True
> 
> End Sub
> 
> Hope this helps!  If so, let me know, click "YES" below.
> -- 
> Cheers,
> Ryan
> 
> 
> "newbie" wrote:
> 
> > Hi, 
> > 
> > I have two questions about Excel VBA and hope any experts can give me a hint
> > 
> > (1) I wonder how to retrieve automatically the previously saved values from 
> > the combo box and a group of radio buttons whenever I open the workbook? 
> > 
> > (2) Everytime I need to insert an object, I copy the object to one worksheet 
> > (named as SLD) from another worksheet (named as Lib) which acts like a 
> > library. However, because of using the function "SELECT",  I encounter 
> > "flickering" on the main sheet (named as Sheet1) each time an object is 
> > inserted. Is there any way that I could eliminate this flickering? Your 
> > advices are greatly appreciated.  
> > 
> > Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
> > Sheets("LIB").Select
> > Select Case myType
> >         Case 1
> >             ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select
> >             Selection.Copy
> >             If myValue = 1 Then
> >                 Sheets("SLD").Select
> >                 ActiveSheet.Paste
> >                 Selection.Name = "offtrfr1x3wdgoff1"
> >                 Selection.Left = 380
> >                 Selection.Top = 642
> >             End If
> >         End Select
> > Sheet1.Activate
> > 
0
Utf
1/7/2010 6:41:03 PM
If the code helped you click the "YES" button next to "Was this post helpful 
to you?  I'm trying to earn my "Silver Wings", lol

Thanks!
-- 
Cheers,
Ryan


"newbie" wrote:

> Thanks Very much Ryan. I have finally solved the problems using your code! 
> Best wishes, Loy
> 
> "Ryan H" wrote:
> 
> > 1.) Put this code in the Workbook module.  Excel doesn't remember what your 
> > last value was for your controls, so what you will need to do is store the 
> > values in cells.  In this example, I stored the value of an option button and 
> > combobox in the Lib worksheet when the workbook is closed.  You can choose 
> > whichever event is best in your application.  And you may need to change the 
> > controls names as well.
> > 
> > Option Explicit
> > 
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >     Sheets("Lib").Range("A1").Value = OptionButton1.Value
> >     Sheets("Lib").Range("A2").Value = ComboBox1.Value
> > End Sub
> > 
> > Private Sub Workbook_Open()
> >     MsgBox "Option Button 1 = " & Range("A1").Value
> >     MsgBox "Combo Box 1= " & Range("A2").Value
> > End Sub
> > 
> > 2.)  To stop the "flickering" just use this:
> > 
> > Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
> > 
> >    Application.ScreenUpdating = False
> > 
> >    ' your code here
> > 
> >    Application.ScreenUpdating = True
> > 
> > End Sub
> > 
> > Hope this helps!  If so, let me know, click "YES" below.
> > -- 
> > Cheers,
> > Ryan
> > 
> > 
> > "newbie" wrote:
> > 
> > > Hi, 
> > > 
> > > I have two questions about Excel VBA and hope any experts can give me a hint
> > > 
> > > (1) I wonder how to retrieve automatically the previously saved values from 
> > > the combo box and a group of radio buttons whenever I open the workbook? 
> > > 
> > > (2) Everytime I need to insert an object, I copy the object to one worksheet 
> > > (named as SLD) from another worksheet (named as Lib) which acts like a 
> > > library. However, because of using the function "SELECT",  I encounter 
> > > "flickering" on the main sheet (named as Sheet1) each time an object is 
> > > inserted. Is there any way that I could eliminate this flickering? Your 
> > > advices are greatly appreciated.  
> > > 
> > > Public Sub OffshoreTrfr(ByVal myValue As Integer, myType As Integer)
> > > Sheets("LIB").Select
> > > Select Case myType
> > >         Case 1
> > >             ActiveSheet.Shapes("Liboff1x3wdgtrfr").Select
> > >             Selection.Copy
> > >             If myValue = 1 Then
> > >                 Sheets("SLD").Select
> > >                 ActiveSheet.Paste
> > >                 Selection.Name = "offtrfr1x3wdgoff1"
> > >                 Selection.Left = 380
> > >                 Selection.Top = 642
> > >             End If
> > >         End Select
> > > Sheet1.Activate
> > > 
0
Utf
1/7/2010 8:34:02 PM
Reply:

Similar Artilces:

Filling drop down box
hi, I have a drop down box in a cell. Based on a value of anohter cell I have to fill my drop down box. what i mean is, say if GreeNPackage is "No" then I want the drop down box to fill a range of values. But if the GreeNPackage is "Yes" then I want the drop down box to fill another set of values. how can i do this? plenty of thanks See reply in .Functions Biff >-----Original Message----- >hi, > >I have a drop down box in a cell. Based on a value of >anohter cell I have to fill my drop down box. what i mean >is, say if >GreeNPackage is ...

data from a modeless dialog box to its parent
I need to post a text data from a modeless dialog box to its parent which is itself a dialog based application. Since it should be a large text data a simple Windows message won't be good. What is the right way to do that? Thanks Gil If you are sure the data will stay current you can post the address of the data (as a pointer) in a windows message to the parent. If you want to be really sure use SendMessage() which will wait until the parent gets the message before returning. You could call a function or copy the data to a variable in the parent dialog, but that could be dang...

Mail merge recipient dialog box
Why are the "edit" and "refresh" buttons at the bottom of the mail merge recipient dialog box grayed out? They were available in the last Word version but I cannot figure how to access them in 7 to add to or correct my mailing list. In Word 2007, the first thing you have to do is select the name of the data source in the box at the bottom left of the dialog box. Then, depending on the type of data source, the Edit and Refresh buttons may be ungreyed. [FWIW the same dialog is used by MS Publisher, where they allow multiple data sources, requiring you to ...

Activities don't show up until we hit the refresh button
Hello all, We upgraded from 3 to 4 and it appears that the upgrade did not complete successfuly, but the users used the system. Everything appears to be working well except for a couple of strange things like when we create an activity the activity does not appear until we hit the Refresh button, going to history and coming back does not fix the issue, only the refresh button any quick fix on this one? thanks ...

Access Code Pushing Values
I have a customer database with [BillAddress] and [ShipAddress]. I am using a "yes/no" box titled[SameShipAddress?]. To automatically fill [ShipAddress] after checking the "yes/no" box I used this code in the after update event for the check box. If Me![SameShipAddress?] Then If IsNull(Me![BillAddress]) Then Else [ShipAddress] = [BillAddress] End If End If This works for the selected customer, but then pushes the entry [BillAddress] of the previous customer to [ShipAddress] of all of the following customers. Any thoughts? Sounds as...

Save a Copy/Overwrite changes dialog box
All of a sudden, this dialog box pops up every time I want to save something in Excel 2002. The box says, "The file '<file name>' may have been changed by another user since you last saved it. In that case, what do you want to do? __ Save a Copy __ Overwrite changes even though I'm the only user that's changed anything in the past 3 years. The shared workbook command has not been changed at all, so that can't be the problem. The settings are the same as the ones I have at home for personal spreadsheets that don't have this dialog box. Help! Than...

Send button --WEBBOT SELF--
I wrote the earlier question and then kept looking for answers. I found the following at MSMVPS.com After the form is submitted the page is redirected to a confirmation page. You cannot change that function nor change the confirmation page. That is not available in Publisher. If this is true then how am I going to get my form page to work. Please help me!!! reference: Publisher web publication forms 101: http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80564.aspx Reference: How to publish a Publisher web in HTTP: http://msmvps.com/blogs/dbartosik/pages/80557.aspx Applicable if yo...

Determine Which Button was Right-Clicked?
All, I am using the code below (started from a sample from one of these groups) to programmatically create a series of buttons in a vertical strip on a form on Form Open. I have a context menu which appears when the user right-clicks any button on the form. How do I "tell" the context menu (really a function on the context menu) which button was right-clicked? I know how to get button attributes when the user left-clicks...but that's not what I need... Is there a way to do this? Thanks, Patrick Code::: :: :: :: :: :: :: :: :: :: :: :: :: :: :: :: :: :: ...

retrieving directory names
Dear group! Apparently it's a simple question: How do I retrieve the directory name that my program's running in? Background: My applications reads and writes its' configurations from an xml file which is supposed to be stored in the same directory with the executable. Relative pathnames like ".\myconfig.xml" appear to work at startup but as soon as I invoke some file dialogs during runtime the ".\" expression is resolved to the last directory I opened something from. The followup-question to this would be: How do I retrieve directory names like "my pictur...

enter value based on drop down list of another field?
I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I created a drop down list in column A based on values in sheet 3, column A (item number). Thisd data comes from sheet 3, where I have column A (Item #'s), column B (Item desc)an Column C (price) In column B of sheet 1, I have Item Descriptions and in column C I have price. I want this to automatically fill in based on what I enter in the column A sheet 1 and it should get the corresponding values from sheet 3 column B and C. I can make the drop down list in sheet 1 column B and C, but some of the descriptions need ...

xp desktop and start button missin
cant get into to windows even in safe mode have to go to task manager to open anytgin In task mgr,locate "new task",type: cmd In cmd type: CHKDSK C: After,you may want to type: Sfc /Scannow Install xp cd,exit the menu page.Once its thru,remove xp cd,type: CHKDSK C: /F Agree to restart,type: EXIT Restart pc. "jen" wrote: > cant get into to windows even in safe mode have to go to task manager to open > anytgin ...

combo box list updating
Hi - I would like my combo box list to change base on the value of A1. That is, I have its input range being B1:F1 - "Year 1", " Year 2" etc. Example: -If cell A1 is the value "10" then the combo box default list item would be "Year 1" (which is cell "B1") -If cell A1 is the value "20" then the combo box default list item would be "Year 2" (which is cell "C1") ETC... Thanks for your help - Jim A ...

"Find a contact box" problem
Hi. I�m having problems with the �Find a contact� box on the Standard toolbar. Previously, I would type in a name and the search would pull up name/s from the Outlook Contacts folder first then, if the name is not in the Contacts folder, it would pull up name/s from the Global Address List if any. Now, when I type in a name, the search only pulls up name/s from the Global Address List. The search does not include the Contacts folder although there are entries in the Contacts folder. The problem started after I transferred a couple of Contacts subfolders to another pst file. How do I fix the p...

need help with list box?
Hi I have a huge table of about 20000 records. how can I restrict entry for a few columns. I am new to excel programming. For example. I have a list of colors (red, green, blue) that I want to go under the Color column. how can I allow the user to click on a pulldown or list box similar to ms access for each cell. thanks in advance. Hi you could use 'Data - Validation'. See: http://www.contextures.com/xlDataVal01.html >-----Original Message----- >Hi > >I have a huge table of about 20000 records. how can I restrict entry for a >few columns. >I am new to exc...

Check box question 04-23-07
I have a form with a check box used to indicate if a receipt is voided or not. What I'm trying to do is go to a new record after the checkbox is marked. I have the following code in place and it works. Kinda. The problem is that if I then go back and UNCHECK the check box, it sends me to a new record again. Here's the code: Private Sub Check43_AfterUpdate() If Ckeck43 = Yes Then DoCmd.GoToRecord , , acNewRec Thanks for any help! End Sub The following works fine for me. If Me.Check3 = True Then DoCmd.GoToRecord , , acNewRec so try If Ckeck43 = True Then DoCmd.GoToRecor...

Stop Buttons showing when opening up form
I have a button on my Main Start up page that when click makes these buttons visible, but when I open up my DB they automatically show on start up, is it possible they not be visible till I click ckbHelp.......Thanks for any Help.....Bob Private Sub ckbHelp_Click() If ckbHelp = True Then cmbHelpNewHorse.Visible = True cmbHelpActFinHorse.Visible = True End If If ckbHelp = False Then cmbHelpNewHorse.Visible = False cmbHelpActFinHorse.Visible = False End If End Sub Bob, In design view, set the property pf the *button* Visible=No Regards/Jacob "Bob" <xxx@xx.xx> wrote i...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

Some contacts don't show on drop down box when addressing email
I was using Outlook Express, my computer motherboard died, got new computer with Windows 7, trying to learn Outlook 2003. The computer tech from my husband's office put Outlook 2003 on the new computer and somehow transferred the addresses that I had in Outlook Express. I do not know what method he used to get the addresses into Outlook 2003. Some things carried over just fine, some things didn't, i.e. a group list of my subdivision property owners, so I had to re-make that distribution list. When I want to email my daughter, her name or email address is not s...

How to change caption on 'apply' button in a CPropertySheet-derived class?
Hi, Does anyone know how to change the caption on the 'apply' button in a CPropertySheet-derived class? I derived a class CPlotSettingsDialog1. Here is it's OnCreate. int CPlotSettingsDialog1::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CPropertySheet::OnCreate(lpCreateStruct) == -1) return -1; CRect WindowRect; GetWindowRect(&WindowRect); WindowRect.bottom = WindowRect.bottom + 60; MoveWindow(&WindowRect); return 0; } all the code does is make the bottom of the property sheet a bit longer. It still has the 3 default buttons. I need to either re-caption t...

Unable to Retrieve E-mail
After setting up Outlook 2003, I receive the following error message when retrieving my e-mail and do not recieve any of my mail... " Task 'Mary Rose Mail - Receiving' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP). The server responded: +OK 48526 octets' " I can go to Yahoo and set up my POP to successfully retrieve my mail. Even when I test the e-mail account using the tester in Outlook, it says all is well. My ISP is at a loss. ...

Comparing cell values then labeling them based on comparision
Hi. I'm trying to find a function or way to compare two cells and then label them according to if one cell is lesser, greater, or the same as the other. I have a large data sheet where I want to compare many pairs of cells and then have them labeled in this way. For example for each pair where the second cell value is larger than the first it would put greater, where the first is larger it would be lesser, etc. Can anybody help me with this? also feel free to email me at andrew@metiri.com. Thanks so much for your help. > Hi. I'm trying to find a function or way to compar...

Mail Button on ie8
I just started using ie8 and was using hot mail I found out I could use live instead of live hot mail. My question is when I click the mail button it stills shows up hot mail.I set live as my default email but it didn't make any difference On Tue, 15 Dec 2009 13:59:03 -0800, kln wrote: > I just started using ie8 and was using hot mail I found out I could use live ... "Live" what? > ... instead of live hot mail. There are: Windows Live Mail (a desktop client, akin to MS Outlook Express. Windows Live Hotmail (an online email service, accessible by ...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

Shortcut into Name box
Hi there. Using E02 on XP. I'm using the Name box (above A1) and wonder if there isn't a keystroke to 'jump' into it quickly? Thanks in advance for any help! Depends on what you use it for. If you quickly want to jump to a named range, you can also use F5. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Bonnie" <Hambrick@discussions.microsoft.com> wrote in message news:454a01c520c2$f4137fb0$a401280a@phx.gbl... > Hi there. Using E02 on XP. I'm using the Name box (above > A1) and wonder if there isn't a keystroke to 'jump' into ...

Combo box default value multiautocomplete
Hi All i have a subform with 10 item. The first one is a combo box that can choose the value from a list that is linked to a table with many record. This subform is related to the master form with link master field codice,cognome,nome link child field codice,cognome,nome that are the other items that are not visualize in the subform. These link give me the right connection to the master form. Normally when i choose the value from list (table) i place also all the other item in the subform using this code in after update precedure Me.ISEE.Value = Me.ISEE.Column(0) Me.F_Costo_con_M...