Rename Table using AfterUpdate on a ComboBox

[richard_a]

Hi Guys

I have a combobox on one sheet and a data table on another sheet. I'd like to use the combobox selection and AfterUpdate event to rename the data table. I've not been able to manage to get it working and wondered if someone could help?

To clarify, I only have 1 table on sheet2

The code below is what I have so far:

 [FONT='inherit']Private Sub CMB1_AfterUpdate()[/FONT]
[FONT='inherit'] With ThisWorkbook.Worksheets("Sheet2")

   [COLOR=#008000][I]'range G3 is the linked cell from the CMB1 and contains the first 3 letters (Left Function) of the text selected[/I][/COLOR]
.ListObjects(1).Name = Sheets("Sheet2").Range("G3").Value & "Table" [/FONT]

  [FONT='inherit']End With[/FONT]
  [FONT='inherit']End Sub[/FONT]

Many thanks for taking a look

Cheers,

Richard

 

[richard_a]

I'm still really struggling with this one guys if anyone has any ideas?

Thanks,

Richard

 

[Norie]

Richard

Is the code you posted not working?

If so, in what way?

 

[richard_a]

Hi Norie,

No the code doesn't work and when executed the table just remains with the same name as before. When I have the control on the same sheet as the table I want to rename then it works, but this isn't really an option for me as I want to keep the sheet containing the data out of the way (although not hidden)

Thanks,

Richard

 

[Norie]

Is the code definitely being executed?

 

[richard_a]

Yes it's definitely being executed. It was coming up with an error message saying "out of range".

As it was working when the control was on the same sheet as the data table I just included a couple of extra lines of code to activate the sheet containing the data table prior to executing the renaming. Not sure why I didn't do this earlier, working fine now.

Thanks for replying to my post

Richard

 

[Norie]

Richard

You shouldn't need to activate/select the sheet as long as you reference things properly.

Can you post your working code and/or details of which sheet(s) the table and combobox are located on?