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

 

[mattbird]

Is the Immediate window found in the view tab? and do I past the code in there? or Application.EnableEvents? if so neither work.

 

[Smitty]

You can goto View-->Immediate Window or just hit CTRL+G. It will open beneath the code window. From there type in ?Application.EnableEvents and hit Enter. It will display True/False beneath it. If it's False then just type in: Application.EnableEvents = True and you should be good to go.

 

[mattbird]

with regards to sharing, how do I set up the workbook to share data? for example if I input a new weight it will show on another work book even if I have closed mine and the other person opens there's later it still displays the new weight. I have been linking workbooks but both workbook have to be open for any data transfer.

 

[Smitty]

See this post from the Excel Team:

Excel Blog - Real-time co-authoring in the Excel Web App: Why and how we did it

Just note that the Excel Web App doesn't support VBA.

 

[mattbird]

I am trying to send data from four cells (Date, weight, BMI and WC) to another sheet, whith the idea that every time data is put into these cells it records them into the table on the other worksheet, this will alow me to colate the data into a line graph so I have feedback on the person if they are loosing weight.
I am having an issue with this code and excel is highlighting a line in yellow (I have highlighted it in Red); I can't work out what is wrong...... Could you please see if you can spot any issues.... Thanks.

Private Sub CommandButton1_Click()
Dim CustomerDate As Date, CustomerWeight As Integer, CustomerBMI As Integer, CustomerWC As Integer
Worksheets("207 Sig Sqn").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(x1Down).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerDate
ActiveCell.Offset(0, 1).Selects
ActiveCell.Value = CustomerWeight
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerBMI
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerWC
Worksheets("207 Sig Sqn").Select
Worksheets("207 Sig Sqn").Range("L17").Select


End Sub

 

[mattbird]

I have sorted that problem it was because I has data in the cell already. The only issue I am having instead of putting BMI and WC into the next columns it putting then in the same row as the weight but below? any idear how to get it to go to the next column instead?

 

[Smitty]

It should be xlDown, not x1Down.

You can also do it without selecting (not tested):

<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><br>    <SPAN style="color:#00007F">Dim</SPAN> CustomerBMI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CustomerWC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        <SPAN style="color:#00007F">With</SPAN> Worksheets("207 Sig Sqn")<br>            CustomerDate = .Range("L17")<br>            CustomerWeight = .Range("G17")<br>            CustomerBMI = .Range("H17")<br>            CustomerWC = .Range("I17")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> Worksheets("Stats 1")<br>            <SPAN style="color:#00007F">If</SPAN> .Range("B5").Offset(1, 0) <> "" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> .Range("B5").End(xlDown)<br>                    .Offset(1, 0).Value = CustomerDate<br>                    .Offset(0, 1).Value = CustomerWeight<br>                    .Offset(2, 0).Value = CustomerBMI<br>                    .Offset(2, 1).Value = CustomerWC<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><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>

 

[mattbird]

How would I write code for a command button to hyperlink to specific worksheet in another workbook?

 

[Smitty]

You don't need code to add a Hyperlink. Just right-click the object and select Hyperlink. I personally prefer to use shapes instead of command buttons, because they have more formatting options.

 

[mattbird]

If I want a control button to open a specific sheet "Stats 1" in another workbook how would I do this? How do you make a shape into a button?

Matt