Microsoft Excel - Combo Boxes Saving data 1

[bluebilly]

I have several lists in an Excel Spreadsheet that I can select records from using combo boxes. How can I save the results of my selections in a separate worksheet.
For instance if I have a list of names in say Worksheet 1 in one Combo box & a list of Activities in the same worksheet in another Combo box - I want to select a single name & a single activity and then save these selections in a separate worksheet. I don't use VB - so an explanation without the use of code woule be very much appreciated.

Thank you

bluebilly

 

[Nikita6003]

Hiya,

have you used the FORMS combobox or the CONTROL TOOLBOX one?
If its the second one, you can set the LinkedCell property - this'll dump whatever text the user selects & is visible in the text box part of the combobox in a cell of your choosing.

The FORMS combobox can also be linked to a cell. BIG difference is - the Linked cell is filled NOT with what you see in the combobox text field, BUT with the index number of whatever you selected.

There is a workaround: add a column to the left of the list that your combobox list is based on & fill this with consecutive numbers.
Say for instance you have this list on Sheet1:

   A      B      C      D
          Tim           3
          Peter
          Sharon

and you've a FORMS combobox on Sheet1, which takes range B1:B3 as the basis for its list and has Cell D1 as its LinkedCell. When you select one of the names, the combobox will return the INDEX - i.e. its position in the list - in cell D3. Select Sharon will return index 3
Now all you do is add the index numbers in column A (just type them in or use the autofill option to let xl do it for you):

   A      B      C      D
   1      Tim           3
   2      Peter
   3      Sharon

Go to the cell where you want to see the result of the combobox selection & add this formula:
=VLOOKUP(Sheet1!D3,Sheet1!B1:B3,2,FALSE)
This'll lookup the number, find the corresponding bit of text, and show the result on sheet 2 ...

HTH!

Cheers
Nikki

 

[bluebilly]

Thanks very much - that's great for recording one line of records. What I now need to do is find a way to compile a table of all records selected using the combo boxes (I'm going with the FORMS combobox at this stage)i.e. when the next user uses the form and selects from the combo box these results are saved under the first line of records.

I appreciate your time & trouble

Regds

 

[bluebilly]

Any ideas how you clear a combo box with the 'Record New Macro' function so that a new user has a blank form to work with ?
Also what is the VB function to move down one cell ?

Many Thanks

 

[Nikita6003]

Hiya,

am working on a soluytion for the list: it involves some VBA and as you're using the FORMS version this is quite fiddly - there's hardly any documentation with it. But bear with me - I'll send you this if you send me your email address (if you don't I'll post the code + explanation here tomorrow but it'll be a mighty big post ;-))

Now as for the other 2 questions:
VBA to move down one cell =

ActiveCell.Offset(1,0).Select

- this offsets your cursor one row down and 0 columns to the right

Clear a combobox: first set the object to type DropDown otherwise you can't do much with it - it's NOT type Combobox - that's the Active-X one (the one from the Control Toolbox)

    Dim l_cboNames As DropDown

    'set combobox so it can be manipulated programatically
    'Code assumes dropdown = on sheet 1
    Set l_cboNames = ThisWorkbook.Sheet(1).DropDowns("Drop Down 1")

Either of the following 2 lines will then clear your combobox
l_cboNames.ListIndex = 0
l_cboNames.Text = ""
[/code]

HTH so for ...

Cheers
Nikki

Nikita6003@Hotmail.com

 

[bluebilly]

Once again, thanks for your reply.
I was really only concerned if I was missing something simple, which by the content of your reply it appears that I am not. Using the VBA command that you have provided I reckon I can overcome most of what I want via simple macros, so please don't spend anymore time on this.

Kind regards

 

[Nikita6003]

Hokay - and it's taught me few things I'd forgotten, anyway.

Cheers
Nikki

 

[bluebilly]

Hello Nikki
Last question honest !
My Forms Combo Box is called DropDown3_Change and has a list of 8 names in it. After the user selects a name and presses a 'Send to Database' macro button to send the info to another worksheet, I want the DropDown_Change Combo Box to clear (I have a 'blank' name as one of the 8 listed if that is relevant - at least I can manually clear the combo, although a 'Record Macro' does not pick up changes to combo boxes strangely).
Is it possible to include some VB in the 'Send to Database' macro (via Edit macro)to achieve this? I have no idea of the syntax - so would really appreciate your help.

Many Thanks

 

[Nikita6003]

Hiya ...

From the name of the dropdown I think your combobx is actually named DropDown3 rather than DropDown3_Change. If your macro is called DropDown3_Change, the combo will be DropDown3. If the macro's named DropDown3_Change_Change, THEN it's called DropDown3_Change ;-)

If your combobox is named

DropDown3_Change

you should be able to use this (if it's not, just substitute the correct name):

ThisWorkbook.Sheets(1).DropDowns("DropDown3_Change").Text = ""

If you've also got a blank name in the list you can try setting the ListIndex property of the dropdown to whatever position the b;ank name in your list (if it's the first elemen, it's index would be 1, etc)

Use this code:

Dim l_drpTest As DropDown

Set l_drpTest = ThisWorkbook.Sheets(1).DropDowns("DropDown3&quot
l_drpTest.ListIndex = 1

The reason I'm using a varable is that you'll get a list of properties and methods in the VBA editor. Excel doesn't show this list if you don't - prob because it's an xl 95 throwback. But at least you'll know what code you can and can't use ... very helpful, I can tell you

Hope this clears things up - Cheers
Nikki

 

[bluebilly]

Hello Nikki, it's me again (Bill),
I think I'm almost there - thanks to you, but seeing as I have never dabbled with VB - I'm having trouble with the code.
The worksheet where the Combo boxes are is called
Book Appointment and the Combo Boxes are called (as you have suggested): Drop Down 3, Drop Down 4, Drop Down 5 etc (note the spaces between Drop & Down & Down & the number).
Applying your method to clear the box(es), I've entered the following to my macro and tried to run it without success - what am I doing wrong ?

Sheets("Book Appointment&quot.Select
Range("A1&quot.Select
DropDowns("Drop Down 3_Change&quot.Text = ""
End Sub

Your continued assistance is very much appreciated.
Rgds

 

[Nikita6003]

Hiya Billy!

Try this instead:

'The DropDown object isn;t in the list you'll get after entering a space after AS - just ignore the vBA editor on this and keep typing!
Dim l_drpTest As DropDown

Set l_drpTest = Sheets("Book Appointment").DropDowns("Drop Down 3_Click")
l_drpTest.Text = ""
End Sub

If that doesn't work, try this variant:

Dim l_drpTest As DropDown

Set l_drpTest = Sheets("Book Appointment").DropDowns("Drop Down 3")
l_drpTest.Text = ""
End Sub

and if that doesn't work, you can always try using the dropDown's index numnber rather than it's name:

Dim l_drpTest As DropDown

Set l_drpTest = Sheets("Book Appointment").DropDowns(1)
l_drpTest.Text = ""
End Sub

And now I'm all out of ideas. If you're still stuck, send me the workbook on Nikita6003@hotmail.com & I'll take a look ;-)

Cheers
Nikki

 

[bluebilly]

Nikki - YES !
The first option works perfectly.
Once again many thanks for all your help.
I hope you won't mind if I contact you again in the future

Rgds

Bill

 

[Nikita6003]

i won't mind if you won't mind ;-)

glad to have helped

chees
Nikki

 

[bluebilly]

Just noticed, having cleared the boxes that I now cannot select from them - they seem fixed as 'blank' !

B

 

[Nikita6003]

... b*gger

try using ListIndex - this'll not touch the textbox part of the combo but will set the index to zero - which for these dropdowns is always a Null string

l_drpDropDown.ListIndex = 0

instead of

l_drpDropDown.Text = ""

To get the text back i nthe combo: save & close, then reopen the workbook ...

Cheers
Nikki

 

[bluebilly]

I changed
l_drpDropDown.ListIndex = 0
for
l_drpDropDown.Text = ""

but that doesn't appear to work.
Could we maybe force each combo box to select the 'blank' option - for instance position 9 for Drop Down 3 when the macro is run?

B

 

[Nikita6003]

Sure - just set listindex to 9

But the ListIndex = 0 option did work in my samplesheet, so I dunno why it's not working in yours ...

Cheers
Nikki

 

[bluebilly]

DOH !
My fault - forgot to save it !
It's been a long day....

Works fine now - thank you so much

Bill

 

[Nikita6003]

yw - and ysure as well?? ;-)

Cheers
Nikki

 

[bluebilly]

Hello again Nikki,
Same workbook - different problem.
I am trying to record a 'Record' Macro that goes to a worksheet and highlights a range for sorting by using the shift and End keys as the range is constantly having records added to it. When I run the macro it substitutes the Shift End Down or Shift End Right instructions with cell references.
For instance, say when I record shift end down and shift end right, the macro thinks it is highlighting say b30..c55 (the initial records), when I need b30..c56. Can you tell me what VB for SHIFT END DOWN AND SHIFT END RIGHT IS PLEASE which I think will solve the problem?
Thanks

Bill