Help with Microsoft excel spreadsheets

scottishlass

Lifeisbutadream wrote: »

How do you mean?

How do you select the contact list at the moment.

I was working on the assumption that you had the same contact list at the start of every workbook you could then change each of these so that they link just to one of them.

So if you created a new workbook contacts.xls then the contactlist in your first workbook could be linked to =[contacts.xls].Sheet1.A1:A1000 etc

Lifeisbutadream

espresso wrote: »

The problem is that only you know exactly what you are trying to achieve. When creating spreadsheets, ease of use and maintainability need to be considered or you will simply make extra work for yourself. It definitely makes sense to only have one contact list to maintain and the quicker you sort it out the better. Did you create a named list called Contacts when you created the Contact List.xls sheet?

Yes I did, but I couldnt get it to work. Problem is I dont want to spend ages trying to make that work when I dont think it is what I need. I really need a drop-down combi-box as I already have now - the spreadsheets I have set up work really well, the only problem is having to update the numerous contacts lists. I really dont think that typing in the name will work for me because I will have to remember exactly how the customers name is typed in to get the address.

espresso

Lifeisbutadream wrote: »

Yes I did, but I couldnt get it to work. Problem is I dont want to spend ages trying to make that work when I dont think it is what I need. I really need a drop-down combi-box as I already have now - the spreadsheets I have set up work really well, the only problem is having to update the numerous contacts lists. I really dont think that typing in the name will work for me because I will have to remember exactly how the customers name is typed in to get the address.

I haven't got the time to investigate at the moment but surely there is a way for your dropdown lists in each sheet to get their info from one maintained Contact List, therefore no need to type names in.

Lifeisbutadream

espresso wrote: »

I haven't got the time to investigate at the moment but surely there is a way for your dropdown lists in each sheet to get their info from one maintained Contact List, therefore no need to type names in.

I am pretty sure there is a way, just hoping that someone knows how..

espresso

Lifeisbutadream wrote: »

I am pretty sure there is a way, just hoping that someone knows how..

Surely it's just an implementation of Chippy's VLOOKUP suggestion in post #2.

You would have to get it to first though! Try on test sheets first and then modify your real sheets.

:rolleyes:

scottishlass

I managed to get the combo box to select a list from another spreadsheet guee it all depends on how you've currently got it set up but you should be able to just change the location and it will work.

Lifeisbutadream

scottishlass wrote: »

I managed to get the combo box to select a list from another spreadsheet guee it all depends on how you've currently got it set up but you should be able to just change the location and it will work.

OK, maybe I am just being really thick, but I have my drop down list set up, right click on it and select 'Format Control', where I get a window asking me 'Input range' which is where I put where the information should be picked up from. I dont know what to put in there to make it pick up from another workbook?

If you managed to make it work, what formula did you have in here? are you working on Windows Vista?

Chippy_Minton

Okay, you need a dropdown so that you can pick a name from your central contacts list. The following code populates a dropdown (combo box) with names and addresses from another workbook, Contacts List.xls. You can then select a name from the dropdown and it puts the name in the active cell.

First paste the following code into a VB Module on one of your spreadsheets (or preferably a copy of it, to see how it works first).

'Change this as appropriate to the full filename of your contacts list
Const Contacts_Workbook = "C:\Documents and Settings\YOURUSERNAMEHERE\My Documents\Contact List.xls"


Sub Load_cbContacts_Button()
    Populate_Contacts_Combobox
End Sub


Public Sub Populate_Contacts_Combobox()

Dim cbContacts As DropDown
Dim wbContacts As Workbook
Dim rngContacts As Variant
Dim lastRowColA As Integer
Dim arrContacts() As String
Dim r As Integer
 
Application.ScreenUpdating = False

Set cbContacts = Worksheets(1).DropDowns("cbContacts")
cbContacts.RemoveAllItems

'Open Contacts workbook as read only and read names (Column A) and addresses (Column B) into array

Set wbContacts = Workbooks.Open(Contacts_Workbook, , True)
lastRowColA = wbContacts.Worksheets(1).Range("A65536").End(xlUp).row
Set rngContacts = wbContacts.Worksheets("Sheet1")
For r = 1 To lastRowColA
    ReDim Preserve arrContacts(1, r - 1)
    arrContacts(0, r - 1) = rngContacts.Cells(r, 1).Value   '1 for Column A
    arrContacts(1, r - 1) = rngContacts.Cells(r, 2).Value   '2 for Column B
Next

'Close Contacts workbook without saving changes (none were made)

wbContacts.Close False
Application.ScreenUpdating = True

'Populate combobox from contacts array

For r = 0 To UBound(arrContacts, 2)
    cbContacts.AddItem arrContacts(0, r) & "," & arrContacts(1, r)
Next
cbContacts.ListIndex = 1

End Sub


Sub Select_Name_From_cbContacts()

'Populate active cell with name selected from contacts combobox

Dim cbContacts As DropDown

Set cbContacts = Worksheets(1).DropDowns("cbContacts")
If cbContacts.ListIndex = 0 Then Exit Sub

ActiveCell.Offset(0, 0).Cells(1, 1).Value = Split(cbContacts.list(cbContacts.ListIndex), ",")(0)

End Sub

On the worksheet, place a Combo Box from the Forms Toolbar. In the Name box, change its name to cbContacts. Assign the macro Select_Name_From_cbContacts to it.

Also place a Button from the Forms Toolbar, assign the macro Load_cbContacts_Button to it and change its name to btnLoadContacts. Change the text on the button to 'Load Contacts' or similar. This button allows you to refresh the contents of the dropdown in case you update the central contacts list.

Finally, in the VB Editor, paste the following code into ThisWorkbook:

Private Sub Workbook_Open()
    Call Populate_Contacts_Combobox
End Sub

If there is already a Workbook_Open subroutine, simply add the Call Populate_Contacts_Combobox to it.

Let me know if you need further help with any of the above.

scottishlass

Lifeisbutadream wrote: »

OK, maybe I am just being really thick, but I have my drop down list set up, right click on it and select 'Format Control', where I get a window asking me 'Input range' which is where I put where the information should be picked up from. I dont know what to put in there to make it pick up from another workbook?

If you managed to make it work, what formula did you have in here? are you working on Windows Vista?

In the input range I just typed in =[nameofotherworkbook.xls].NameofSheet.Range

Lifeisbutadream

Chippy_Minton wrote: »

Okay, you need a dropdown so that you can pick a name from your central contacts list. The following code populates a dropdown (combo box) with names and addresses from another workbook, Contacts List.xls. You can then select a name from the dropdown and it puts the name in the active cell.

First paste the following code into a VB Module on one of your spreadsheets (or preferably a copy of it, to see how it works first).

'Change this as appropriate to the full filename of your contacts list
Const Contacts_Workbook = "C:\Documents and Settings\YOURUSERNAMEHERE\My Documents\Contact List.xls"
 
 
Sub Load_cbContacts_Button()
    Populate_Contacts_Combobox
End Sub
 
 
Public Sub Populate_Contacts_Combobox()
 
Dim cbContacts As DropDown
Dim wbContacts As Workbook
Dim rngContacts As Variant
Dim lastRowColA As Integer
Dim arrContacts() As String
Dim r As Integer
 
Application.ScreenUpdating = False
 
Set cbContacts = Worksheets(1).DropDowns("cbContacts")
cbContacts.RemoveAllItems
 
'Open Contacts workbook as read only and read names (Column A) and addresses (Column B) into array
 
Set wbContacts = Workbooks.Open(Contacts_Workbook, , True)
lastRowColA = wbContacts.Worksheets(1).Range("A65536").End(xlUp).row
Set rngContacts = wbContacts.Worksheets("Sheet1")
For r = 1 To lastRowColA
    ReDim Preserve arrContacts(1, r - 1)
    arrContacts(0, r - 1) = rngContacts.Cells(r, 1).Value   '1 for Column A
    arrContacts(1, r - 1) = rngContacts.Cells(r, 2).Value   '2 for Column B
Next
 
'Close Contacts workbook without saving changes (none were made)
 
wbContacts.Close False
Application.ScreenUpdating = True
 
'Populate combobox from contacts array
 
For r = 0 To UBound(arrContacts, 2)
    cbContacts.AddItem arrContacts(0, r) & "," & arrContacts(1, r)
Next
cbContacts.ListIndex = 1
 
End Sub
 
 
Sub Select_Name_From_cbContacts()
 
'Populate active cell with name selected from contacts combobox
 
Dim cbContacts As DropDown
 
Set cbContacts = Worksheets(1).DropDowns("cbContacts")
If cbContacts.ListIndex = 0 Then Exit Sub
 
ActiveCell.Offset(0, 0).Cells(1, 1).Value = Split(cbContacts.list(cbContacts.ListIndex), ",")(0)
 
End Sub

On the worksheet, place a Combo Box from the Forms Toolbar. In the Name box, change its name to cbContacts. Assign the macro Select_Name_From_cbContacts to it.

Also place a Button from the Forms Toolbar, assign the macro Load_cbContacts_Button to it and change its name to btnLoadContacts. Change the text on the button to 'Load Contacts' or similar. This button allows you to refresh the contents of the dropdown in case you update the central contacts list.

Finally, in the VB Editor, paste the following code into ThisWorkbook:

Private Sub Workbook_Open()
    Call Populate_Contacts_Combobox
End Sub

If there is already a Workbook_Open subroutine, simply add the Call Populate_Contacts_Combobox to it.

Let me know if you need further help with any of the above.

Thank you for going to all this trouble.

When I set up all the original sheets I was using Excel 97-2003 and found is straightforward. I am now using windows vista and I have no idea how to insert a dropdown box! the drop down boxes are already there - I appear to have everything OK in VB, but cant get any further than that.