Having trouble sharing Excel tables with other workbooks?

[mattbird]

Hi,

Is there anyone how knows how to share an Excel table? I have a table which I would like to share with other workbooks but when I click the share workbook it says:

'This workbook can not be shared because it contains Excel tables or XML maps. To share this workbook, convert the table ranges and remove the XML maps.'

I would like to use the Excel table as it automatically transfer formulas to the next row, if I convert the table range, I will have to copy and past my formulas into a new row every time I add one. I am not sure what XML maps are?

I am currently using Excel 2007 on Win8.

Please could someone help......

Matt

 

[Smitty]

Just right-click the shape and you'll see an option for Hyperlink. Then you can navigate to the external workbook.

 

[mattbird]

I also have two issues I am hoping you may be able to help me with? I have added two control buttons, the first button opens another worksheet in the workbook:

Private Sub CommandButton2_Click()
Application.Goto Worksheets("Stats 1").Range("B6")

End Sub

but when I hide the sheet, I want it to open, but it shows the following error:

Run-time error '1004':
Method 'Goto'of object'_Application'failed

How do I get this to work?

The other button sends data to the same worksheet and puts it into another table which then shows the results in a graph:

Private Sub CommandButton1_Click()
Dim CustomerDate As Date, CustomerWeight As Integer, CustomerBMI As Integer, CustomerWC As Integer
Worksheets("Department1").Select
CustomerDate = Range("L17")
CustomerWeight = Range("G17")
CustomerBMI = Range("H17")
CustomerWC = Range("I17")
Worksheets("Stats 1").Select
Worksheets("Stats 1").Range("B5").Select
If Worksheets("Stats 1").Range("B5").Offset(1, 0) <> "" Then
Worksheets("Stats 1").Range("B5").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerWeight
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerBMI
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerWC
Worksheets("Department1").Select
Worksheets("Department1").Range("G18").Select


End Sub

but when I hide the sheet, I want it to open and again it shows an error:

Run-time error '1004':
Select method of Worksheet class failed

How do I get around this?

Matt

 

[Smitty]

You need to unhide the sheet before you can activate it:

Sheets("Stats 1").Visible = True

As for the other code, there's no need to select:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> CustomerDate <SPAN style="color:#00007F">As</SPAN> Date<br>    <SPAN style="color:#00007F">Dim</SPAN> CustomerWeight <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, CustomerBMI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, CustomerWC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Department1")<br>        CustomerDate = .Range("L17").Value<br>        CustomerWeight = .Range("G17").Value<br>        CustomerBMI = .Range("H17").Value<br>        CustomerWC = .Range("I17").Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    lr = Worksheets("Stats 1").Cells(Rows.Count, "B").End(xlUp).Offset(1).Row<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Stats 1")<br>        .Cells(lr, 0).Value = CustomerDate<br>        .Cells(lr, 1).Value = CustomerWeight<br>        .Cells(lr, 2).Value = CustomerBMI<br>        .Cells(lr, 3).Value = CustomerWC<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Smitty]

Note that if you're not selecting the sheet, there's no need to make it visible either.

 

[mattbird]

The code works

Sheets("Stats 1").Visible = True

But I don't want the tab to stay visible if that is possible, I want it to open when the button is clicked. At present the tab is hidden and the code allows it to open but the tab stays which I would like it to hide once I click back on the main worksheet tab.

 

[Smitty]

Then you can use the Worksheet_Deactivate event to rehide it.

 

[mattbird]

Hi,

I'm not sure how and where to put the Worksheet_Deactivate event in the code?

Private Sub TextBox2_Click()
Sheets("Stats 1").Visible = True
Application.Goto Worksheets("Stats 1").Range("B6")

End Sub

I also took your advice and changed my command button to a shape (text box) and used the code you gave me but it comes up with an error "Run-time error '1004' application-defined or object-defined error. I have highlighted the part of code in bold.

Private Sub TextBox1_Click()
Dim CustomerDate As Date
Dim CustomerWeight As Long, CustomerBMI As Long, CustomerWC As Long, lr As Long

With Worksheets("BCM Database")
CustomerDate = .Range("R15").Value
CustomerWeight = .Range("M15").Value
CustomerBMI = .Range("N15").Value
CustomerWC = .Range("O15").Value
End With

lr = Worksheets("Stats 1").Cells(Rows.Count, "B").End(xlUp).Offset(1).Row

With Worksheets("Stats 1")
.Cells(lr, 0).Value = CustomerDate
.Cells(lr, 1).Value = CustomerWeight
.Cells(lr, 2).Value = CustomerBMI
.Cells(lr, 3).Value = CustomerWC
End With

End Sub

With TextBox1 it has the same issue that if the "Stats 1" worksheet is hidden it shows a Run-time error '1004'. Again I want it to place the data into the Stats 1 sheet but the tab to remain hidden.

Your help is greatly appreciated.

 

[mattbird]

I have got TextBox1 working I had to change .Cells(lr, 0).value=CustomerDate to range 2-5 and it works if the sheet is hidden which is great, but still can't work out the code to view "Stats 1" whilst keeping the tab hidden.

Sub TextBox2_Click()
Sheets("Stats 1").Visible = True
Application.Goto Worksheets("Stats 1").Range("B5") - B5 is the first cell data goes into on the stats sheet all the rest of the sheet is protected so had to use that range?