+ Reply to Thread
Results 1 to 9 of 9

VBA to set combobox linked cell to cell on a different sheet

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    VBA to set combobox linked cell to cell on a different sheet

    So I have a set of code that copies an existing sheet that contains comboboxes. I want the comboboxes in the new sheet to have their 'linked cells' to the original sheet - so that the combobox values always match, you change one and the other changes too.

    The code I have is below... the weird thing is, that code works when it's using a cell on the active sheet but doesn't when I'm referring to a cell on a different sheet. I've also tried naming the cells as ranges and setting the linked cells to the ranges but that hasn't worked either.

    Private Sub CommandButton4_Click()

    'Insert new Flash Pivottable

    Application.DisplayAlerts = False
    On Error Resume Next
    Dim flash As Worksheet
    Set flash = Worksheets("Flash Pivot").Copy(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    Call ThisWorkbook.CheckCaches

    flash.Select
    flash.ComboBox1.LinkedCell = Worksheets(Sheet9).Range("F1")
    flash.ComboBox2.LinkedCell = Worksheets(Sheet9).Range("G1")
    flash.ComboBox3.LinkedCell = Worksheets(Sheet9).Range("J1")
    flash.Range("C8").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: VBA to set combobox linked cell to cell on a different sheet

    I think that the proper syntax should be:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    Re: VBA to set combobox linked cell to cell on a different sheet

    Hi Sbarro79,

    Thanks for the response - that still isn't working however.

    When I go into the properties of the combobox I can manually set the sell using the reference above but it just doesn't want to be set by VBA!

    Any further suggestions?

    Cheers,

    Chris

  4. #4
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: VBA to set combobox linked cell to cell on a different sheet

    Ok, try this:

    Please Login or Register  to view this content.
    Last edited by Sbarro79; 07-11-2016 at 12:19 PM.

  5. #5
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    Re: VBA to set combobox linked cell to cell on a different sheet

    Still not working :/ any other thoughts?

  6. #6
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: VBA to set combobox linked cell to cell on a different sheet

    Can you comment application.displayalerts and on error resume code lines to see if the code produces any errors?
    Can you also attach a sample worksheet (with fake data) to see why code is not working.

  7. #7
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    Re: VBA to set combobox linked cell to cell on a different sheet

    No errors are generated I've attached the whole spreadsheet - the relevant macro button is the 'Insert Flash Pivot Table' button. You shouldn't need to but if you do - any locked sheets have the password 'outlook'.

    Really appreciate your time, thanks.

    Chris
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: VBA to set combobox linked cell to cell on a different sheet

    Right, maybe this time I'll be more successful :P

    First the code:

    Please Login or Register  to view this content.
    Now it's time for some adjustments:
    1. I had to change Flash Pivot sheet name to FlashPivot (linkedcell doesn't want to accept names with white spaces). This was the first issue I didn't know before: Sheet9 was not a worksheet name but number, while the name was Flash Pivot.
    2. I noticed, that there are two hidden worksheets (Dec sold... and dec opport...). I had to move them before CV Revenue sheet, otherwise code was referring to one of them instead of the copied one. And that was the second issue.

    Well, at least the code worked for me, although it usually took about 30-60 seconds for comboboxes to be updated.
    Hope it will work for you as well.

  9. #9
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    Re: VBA to set combobox linked cell to cell on a different sheet

    You're a beautiful person and I thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combobox not reflecting linked cell value
    By piipy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2016, 12:11 AM
  2. [SOLVED] ActiveX ComboBox Linked Cell Empty Value
    By sjmxls2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2016, 12:08 PM
  3. Combobox not reading linked cell
    By colangeloc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2016, 05:00 AM
  4. [SOLVED] ComboBox Linked Cell
    By fredlo2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2015, 11:31 AM
  5. ComboBox linked to different cell
    By NJH88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2014, 09:22 AM
  6. ComboBox linked cell problem
    By minvai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2014, 09:20 AM
  7. ComboBox on worksheet linked to a cell
    By dinamis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2014, 05:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1