Combobox value populate cell selection

I am looking for button code to have a selected cell range merged and
populated with value chosen from combobox. This value is centered in
the merged cell selection.

The cells range is defined manually with mouse.


Bart

0
9/24/2010 5:50:56 AM
excel 39879 articles. 2 followers. Follow

3 Replies
823 Views

Similar Articles

[PageSpeed] 26

Have a look at this event code which you can refine.

No error-checking for data in the selected range...........assumes the
mergerange is empty when selected.

Runs when a value is selected from Combobox1

Private Sub ComboBox1_Change()
Set srng = Application.InputBox(prompt:= _
            "Select A Range", Type:=8)
With srng
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .MergeCells = True
        .Value = ComboBox1.Value
    End With
End Sub


Gord Dibben     MS Excel MVP

On Thu, 23 Sep 2010 22:50:56 -0700 (PDT), AA Arens <bartvandongen@gmail.com>
wrote:

>I am looking for button code to have a selected cell range merged and
>populated with value chosen from combobox. This value is centered in
>the merged cell selection.
>
>The cells range is defined manually with mouse.
>
>
>Bart
0
phnorton (279)
9/24/2010 6:47:36 PM
On 25 Sep, 01:47, Gord Dibben <phnor...@shaw.ca> wrote:
> Have a look at this event code which you can refine.
>
> No error-checking for data in the selected range...........assumes the
> mergerange is empty when selected.
>
> Runs when a value is selected from Combobox1
>
> Private Sub ComboBox1_Change()
> Set srng =3D Application.InputBox(prompt:=3D _
> =A0 =A0 =A0 =A0 =A0 =A0 "Select A Range", Type:=3D8)
> With srng
> =A0 =A0 =A0 =A0 .HorizontalAlignment =3D xlCenter
> =A0 =A0 =A0 =A0 .VerticalAlignment =3D xlCenter
> =A0 =A0 =A0 =A0 .MergeCells =3D True
> =A0 =A0 =A0 =A0 .Value =3D ComboBox1.Value
> =A0 =A0 End With
> End Sub
>
> Gord Dibben =A0 =A0 MS Excel MVP
>
> On Thu, 23 Sep 2010 22:50:56 -0700 (PDT), AA Arens <bartvandon...@gmail.c=
om>
> wrote:
>
> >I am looking for button code to have a selected cell range merged and
> >populated with value chosen from combobox. This value is centered in
> >the merged cell selection.
>
> >The cells range is defined manually with mouse.
>
> >Bart

Thanks Gord. It going in the right direction.

What I finally looking for is the user has to set two combo values and
one toggle button value (Y/N) and that this string (value A, value B
and Y/N) is populated in a cell range, merged and centered.

It is possible to set the mouse selection without the "Set a range"
inputbox? So the user select the cells and a command only performs the
population process based on the chosen values.

Bart







0
9/25/2010 4:38:16 AM
On 25 Sep, 11:38, AA Arens <bartvandon...@gmail.com> wrote:
> On 25 Sep, 01:47, Gord Dibben <phnor...@shaw.ca> wrote:
>
>
>
> > Have a look at this event code which you can refine.
>
> > No error-checking for data in the selected range...........assumes the
> > mergerange is empty when selected.
>
> > Runs when a value is selected from Combobox1
>
> > Private Sub ComboBox1_Change()
> > Set srng =3D Application.InputBox(prompt:=3D _
> > =A0 =A0 =A0 =A0 =A0 =A0 "Select A Range", Type:=3D8)
> > With srng
> > =A0 =A0 =A0 =A0 .HorizontalAlignment =3D xlCenter
> > =A0 =A0 =A0 =A0 .VerticalAlignment =3D xlCenter
> > =A0 =A0 =A0 =A0 .MergeCells =3D True
> > =A0 =A0 =A0 =A0 .Value =3D ComboBox1.Value
> > =A0 =A0 End With
> > End Sub
>
> > Gord Dibben =A0 =A0 MS Excel MVP
>
> > On Thu, 23 Sep 2010 22:50:56 -0700 (PDT), AA Arens <bartvandon...@gmail=
..com>
> > wrote:
>
> > >I am looking for button code to have a selected cell range merged and
> > >populated with value chosen from combobox. This value is centered in
> > >the merged cell selection.
>
> > >The cells range is defined manually with mouse.
>
> > >Bart
>
> Thanks Gord. It going in the right direction.
>
> What I finally looking for is the user has to set two combo values and
> one toggle button value (Y/N) and that this string (value A, value B
> and Y/N) is populated in a cell range, merged and centered.
>
> It is possible to set the mouse selection without the "Set a range"
> inputbox? So the user select the cells and a command only performs the
> population process based on the chosen values.
>
> Bart

Anybody able to assist me?
0
9/29/2010 6:20:55 AM
Reply:

Similar Artilces:

Requery combobox
I have a form that includes a combobox that has the following in its on enter event: Private Sub cbProjectPhase_Enter() Me.cbProjectPhase.Requery End Sub The query that is tied to this combox uses another field ("Cost Center") on this form as criteria to select records for this combobox. It works fine except when I go to the next record and click on that combobox it removes what is showing in the previous record's combobox's field because my selection "Cost Center" is different this time. I understand why this happens but I don't know how to get around it. I...

Formatting for blank cell
I have a workbook that is fairly text based in Excel. One worksheet 1, the user has to fill in all kinds of client info (Client name, address, zip, etc...). I want worksheet 2 to pull from worksheet 1 if there is text to pull from, but be blank if there isn't. Example: I have a "Client Name" cell on worksheet 1 & 2. On worksheet 2, I want that field equal to the field on worksheet 1, if there is text in it. If it's blank, I want my field to be blank. I tried just doing the "=MyCellNumberFromPage1", which works if there is text on worksheet 1, but puts in a...

Clearing Cells
Hi I'm using an Excel sheet for pricing items customers purchase. In A2 I could enter any of 4 prices e.g. £30 , £15, £10, £5 In B2, C2, D2, E2, I have entered the following formula =IF(A2=Rate_30,A2,0) It follows that B2 shows £30 and C2,D2,E2 show 0.00 that works ok the problem is with 200 rows its a lot of zero's is it possible to add to the formula something that clears all the zero's so that the columns are easier to read. This sheet is used on a daily basis so the customer could call in tomorrow and buy something at £15 the £30 would c...

Macro copies values in other workbook
Hi, I need a macro that copies two results of workbook1 in two different cells of workbook2. Example: I have in A1 a value result of a sum function and in A2 another value result of another sum function. What I need to do is to copy and paste both values in workbook2. But the A1 value in c1 (workbook2) and A2 value in d1 (workbook2) as values and not as formula. Details: Workbook2 will be closed. If c1 and d1 from workbook2 have any value, then the macro should paste values in the next empty row C2 and d2 for example. Thank you so much for your help! See http://www.rond...

combobox
hi i am trying to create a combobox containing a list of names i want the list to default to a "dummy" value when the workbook is opened How do i do this? thanks kevin You would need a macro for that, assume that you use the combo box from the control toolbox, and that the linked cell is A2 in Sheet2 Private Sub Workbook_Open() Worksheets("Sheet2").Range("A2").Value = "Your_Dummy_Value" End Sub Right click the little excel icon to the left of the filemenu and select view code or press Alt + F11 and double click ThisWorkbook That's where the ma...

Populate combobox
Hello Why doesn't this populate my ActiveX combobox? Private Sub ComboBox1_Change() Dim row As Integer ' Make sure the RowSource property is empty Sheet1!ComboBox1.RowSource = "" Sheet1!ComboBox1.RowSource = "Sheet1!A1:A12" ' Add some items to the ActiveX combobox For row = 1 To 12 Sheet1!ComboBox1.AddItem Sheets("Sheet1").Cells(row, 1) Next row End Sub Change the exclamation point (!) to a period (.) after Sheet1 everywhere except in the row source reference that is within the quote marks. &quo...

This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found the message traffic extremely valuable. After this summers fiasco with the update to MSN Money locking us out of our local data file, I thought, or was hoping M$ would make an extra effort to make sure that Money 2005 was a significant improvement and without the assorted problems that I have found posted here. Some still unresolved. I have made my decision and I do not care how much work it is going to take me, bt I am going back to Quicken after giving Money a two year trial. This program is just not ready f...

Help With Simple Combobox Programming
I have a combo box on sheet1. When the user clicks on the combo box the first time ( it gets focus ) It should add/load all items from sheet2 column A. Now when the user selects from the combobox that item should be copied/placed on sheet1.A5 similarly the next item selected in the combo box should be placed below A6 and so on How can this be accomplished with code thx Hi, Use code like that : Private Sub cboIn_Click() Dim intR As Integer intR = Range("a4").CurrentRegion.Rows.Count Range("a4").Offset(intR, 0).Value = cboIn.Value End Sub Priv...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

Save file with cell name
Hi In cell A1 i have =cell"filename"A1 giving me the I want to run macro that will save new file with data in A1 Example. tab named wc0701. new file saved as wc0701 Any idea's regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200601/1 You could just drop the formula in A1 and use the worksheet name itself. with activesheet .parent.saveas filename:="C:\" & .name & ".xls", ...rest of options end with "Brian Thompson via OfficeKB.com" wrote: > > Hi > In cell A1 i have =cell"f...

combobox into another combobox
i got 2 combo boxes in a worksheet. i want to link the 2 combo boxes together. combo2 will depend on which is selected to the combo1. eg: combo1 - accessories, card, ram list in combo2 will vary on the item selected from the combo1. let say: combo1 - accessories have been selected combo2 - slot fan, usb to ps2 convertor. can someone helps me!!! thxns in advance Hi Take a look at Debra Dalgleish's site for lots of information on Data Validation http://www.contextures.com/xlDataVal13.html and for the section using Combo boxes http://www.contextures.com/xlDataVal10.html -- Regards ...

Pie Chart and Zero Value Labels
Morning All Is there a feature to remove all the zero value labours in a pie chart. I have 8 categories and 5 have a 0 value so it looks very messy. I can't find the feature but I'm sure it must exist. Thanks in advance to all who reply. Regards, H Hi, You could use autofilter to hide the rows of those with a zero value. Or formula as described here, http://www.andypope.info/charts/piezeros.htm Cheers Andy Hardip wrote: > Morning All > > Is there a feature to remove all the zero value labours in a pie chart. I > have 8 categories and 5 have a 0 value so it loo...

Filling More than one field a combobox selection
I have a simple Address Form Suburb, State, PostCode Suburb is a combobox linked to a Post code table What I would like to do is for the user to look up Suburb by typing into the combo box and populate the state and postcode fields on selection. Am I going about this the right way? See www.allenbrowne.com. He has an excellent search function exactly like you need. -- Milton Purdy ACCESS State of Arkansas "Avid Fan" wrote: > I have a simple Address Form Suburb, State, PostCode > > Suburb is a combobox linked to a Post code table > &...

F2
I have recently upgraded from Excel 2000 to Excel 2003. In previous versions i have been able to press the F2 button to edit the text or figures directly in the cell. Since upgrading, i have not been able to do this but have had to use the double-click method, which i find slow. Can anyone tell me why i can no longer use the F2 button? For your information, i have tried turning the 'Edit Directly In Cell' on and off under options, but this does not make any difference. I look forward to hearing from you. Many thanks Tools, Options, Edit, turn OFF edit directly in ...

Averaging Selected Records
Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

Help with ComboBox
Hi, I'm using Excel 2003 I have a spreadsheet using a ComboBox that allows selection of one of several sets of data. Each set is a 3 column row of data. I have the following properties set: BoundColumn: 2 TextColumn: 1 When a selection is made the ComboBox displays the column 1 value. The ComboBox seems to behave normally, except that when I close and open the file the combox initially displays the value of column 2 (instead of column 1). When a new selection is made it goes back to displaying the column 1 data. Shouldn't it always display the data as assigned by TextColu...

How to select same ranges in multiple worksheets?
I have a workbook that consists of all the months. I want to select only certain ranges in each worksheet to change the cell format and text alignments. However, I have to do this on all twelve months, which gets to be a pain. Is there a way to select a certain range/ranges on one sheet and duplicate that selection on the rest of the worksheets throughout the workbook? Thanks --- Message posted from http://www.ExcelForum.com/ Eckaner, Select the sheets together (click the first ; hold control while clicking the other sheets) Then make your range selection and formatting etc. The chang...

Changing colors of cells to establish value.
Hello, Can I program my cells to establish a color of the cell and/or the color of the font according to the value of the cell. ie: If I have a formula that has a product being sold at 5000.00 with a cost of 4000.00, in another 5000.00 sale with a 4500.00 cost, and in another a 5000.00 sale with a 6000.00 cost. Could I show these with the 1000.00 profit showing in one color automatically, if the cost changed to the second, could I show the 500.00 profit as another. If it changed to the 1000.00 loss, could it change to another color? thanks, Mark. You want CONDITIONAL FORMATING....

Some recalcitrant custom cell styles
Hi, I had a problem with multiple custom cell styles that don't allow me to convert an excel 2007 file to excel 2003. I found a VBA example to delete all those custom cell styles easily: Sub style_remove() On Error Resume Next Dim mpStyle As Style For Each mpStyle In ActiveWorkbook.Styles If Not mpStyle.BuiltIn Then mpStyle.Delete End If Next mpStyle End Sub But there are some recalcitrant custom cell styles like: 386grabber=3DVGA.3GR _100301_VC outlook Oral Care France shell=3Dprogman.exe m AeE=AD [0]_INQUIRY =BF=...

match column for value and return next cell value
Hi, I have simple excel with one column having the Part name and secon having the Price. I want a macro which will return SUM of all the part prices user ha selected. My data looks simply like below PART NAME PRICE NKT 1000 NKU 2000 NMP 150 NPG 299 NWT 3495 What i want is if user enters NKT,NKU then it should return 3000 if user enters NKT,NKu,NMP then 3150. Can anyone please help as I am an end user and have no idea of macros. Thanks, kogant -- Message posted from http://www.ExcelForum.com Hi I'd suggest you use pivot tables for this and select the relevant item s within the p...

up down bar value (difference)
Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "...

Linking dates and values
I am trying to find the percent difference between two values. Eac value is the last work day of the month. My spreadsheet lists dates i column A and values in columns B-AI. How can I use the date to searc for the relative value? Thank -- seanbrow ----------------------------------------------------------------------- seanbrown's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2651 View this thread: http://www.excelforum.com/showthread.php?threadid=39782 On Mon, 22 Aug 2005 11:47:58 -0500, seanbrown <seanbrown.1u6duf_1124730421.4036@excelforum-nospam.com> ...

reference value in a cell
I have these values in sheets. Sheet2!A1 = 5 Sheet3!A1 = 7 Now, I put Sheet1!A1 = "Sheet2" which can be varied to "Sheet3" or "Sheet4" etc. User has to provide the name of the sheet here in Sheet1!A1. The problem is what formula to put in Sheet1!A3 so that it checks the value in Sheet1!A1 and depending on sheet name, takes the value of A1 of that sheet. Ofcourse it is wrong, but, something like this [Sheet1!A1]![A1] i.e. if I write Sheet1!A1 = "Sheet3", the value in Sheet1!A3 becomes =7. Any help is appreciated. Try this: =INDIRECT(A1&"...

Increase cell range by percentage
I have a range of cells with values I need to increase by a percentage. Is there a simple way to do that? Thanks, If you want to increase by 5%: Format an empty cell as Number. Enter the number 1.05. Edit>Copy. Select your range. Edit>Paste Special, check Multiply. Make a copy of your workbook before you try. -- Kind regards, Niek Otten "SpaceCamel" <SpaceCamel@discussions.microsoft.com> wrote in message news:DC0214AA-D314-4F74-929B-4230166CF1B4@microsoft.com... >I have a range of cells with values I need to increase by a percentage. > > Is there a simpl...

combobox in vista
hi , guys i meet the the same issue with below http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=ComboBox+in+vista&lang=en&cr=&guid=&sloc=en-us&dg=microsoft.public.platformsdk.shell&p=1&tid=9d3eeb9e-54d2-457d-8fc0-2473b2e2b203 who can tell me how to solve this question, or must wait the next version of mfc welcome any idea. thanks in advance. Doesnt the workaround as mentioned in the thread work for you? -- Ajay "John" <John@discussions.microsoft.com> wrote in message news:D9722557-9FD2-4193-AE3C-AED94F3162CA@microso...