Solved

excel active sheets

Posted on 2011-09-19
2
334 Views
Last Modified: 2012-06-27
I have a worksheet as an object in a Word document.

There are three sheets.

When the worksheet is embedded in an object it seems to need to know which sheet it is dealing with at any time when you refer to a range of cells. So I need to refer to Sheet1. Range etc.

Rather than do it this way is it possible to do it with say worksheet.active sheet. range etc.
This would save me a lot of coding as the code is pretty much the same across all three sheets.

What would be the correct wording for active sheet etc.


0
Comment
Question by:topUKlawyer
2 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 150 total points
ID: 36562597
It's not clear what difficulty you are having, but it might help you to know that the ActiveSheet is a property of the Excel Application and of the ActiveWorkbook, which itself is a property of the Excel Application.

Perhaps you should show us the relevant bits of your code.
0
 
LVL 5

Accepted Solution

by:
slycoder earned 350 total points
ID: 36563086
If you are coding in excel:

    Dim myCell As Range
    For Each myCell In ActiveWorkbook.ActiveSheet.Range("a1:d5")
        myCell.Value = "x"
    Next myCell


will populate cells a1 - d5 with an "x"

if coding in Word:

1) I used the example above and changed the values to "y" and inserted the worksheet into a word document.


2) you need to add the "Microsoft Excel 14.0 Object Library" Reference with Tools - References and use this code:


Public Sub ChangeValues()
    ' Source help:  http://vbadud.blogspot.com/
    Dim objWorkBook As Excel.Workbook
    Dim objInlineShape As InlineShape
    Dim myCell As Excel.Range
   
    On Error Resume Next
    ' Error occurred because one of my shapes is clipart
   
    For Each objInlineShape In ActiveDocument.InlineShapes
        If InStr(1, objInlineShape.OLEFormat.ProgID, "Excel") Then
            objInlineShape.OLEFormat.Activate
            Set objWorkBook = objInlineShape.OLEFormat.Object
           
            For Each myCell In objWorkBook.Sheets(1).Range("a1:d5")
                myCell.Value = "x"
            Next myCell
           
        End If
    Next objInlineShape
   
End Sub


Hope this helps.

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now