Prince 發問時間: 電腦與網際網路軟體 · 9 年前

請問excel macro 如何回到原來儲存格的問題?

請問excel macro ,在各個sheet執行一段程式之後,如何回到原來sheet儲存格的問題?

我想寫一個小巨集,先抓取目前active sheet的active cell位址,然後從目前sheet,搜尋所有sheets的文字方塊,變更所有文字方塊的屬性值之後,再回到原先的sheet之active cell。請問該如何進行?

我已經寫了這一段(其中有一部分也是參考yahoo知識的唷!)

但是,這程式無法回到原先的sheet之active cell。

Sub JJ()

Dim aa As Worksheet

For Each aa In Sheets

aa.Select

If aa.Shapes.Count > 0 Then

ActiveSheet.Shapes.SelectAll

With Selection

.Placement = xlMove

.PrintObject = True

End With

End If

Next

End Sub

已更新項目:

點不進去回應

只好在此回應

兩位的九解答都ok了

贈點20啊

謝謝啊

2 個解答

評分
  • 9 年前
    最佳解答

    Sub JJ()

    Dim aa As Worksheet, shp As Shape

    For Each aa In Sheets

    If aa.Shapes.Count > 0 Then

     For Each shp In aa.Shapes

       With shp

         .Placement = xlMove

         .ControlFormat.PrintObject = True

       End With

     Next

    End If

    Next

    End Sub

    2011-08-24 10:20:51 補充:

    Sub JJ_2()

    Dim aa As Worksheet

    For Each aa In Sheets

     If aa.Shapes.Count > 0 Then

      With aa.DrawingObjects

        .Placement = xlMove

        .PrintObject = True

      End With

     End If

    Next

    End Sub

    2011-08-29 13:34:52 補充:

    For Kubi大:

    正解,請上答吧!

    主需求:各工作表繞一圈再回原工作表

    2011-08-30 11:00:53 補充:

    EXCEL VBA.設定檔案中全部工作表〔物件〕的〔屬性〕

                             <.准提部林.>

    ----------------------------------

    ■巨集1:

     因以〔錄製〕方式取得程式碼,皆免不得會有〔Select〕,

     故執行時須將各工作表轉換為〔當前視窗〕,

     若要跳回原來工作表,則須先設定〔變數〕指定工作表。

    (意見001.Kubi大的方法)

     Sub 物件屬性設定1()

     Dim xSht As Worksheet, xR As Range, MySht As Worksheet, MyRng As Range

     Set MySht = ActiveSheet '巨集執行時的工作表

     Set MyRng = ActiveCell '當前儲存格

     For Each xSht In Sheets

       xSht.Select

       Set xR = ActiveCell '各工作表當前儲存格

       If xSht.Shapes.Count > 0 Then

         ActiveSheet.Shapes.SelectAll

         With Selection

           .Placement = xlMove

           .PrintObject = True

         End With

         xR.Select '離開物件的選取,回選當前儲存格

       End If

     Next

     '返回原工作表及儲存格

     MySht.Activate

     MyRng.Select

     End Sub

    ■巨集2:

     Sub 物件屬性設定2()

     Dim aa As Worksheet, shp As Shape

     For Each aa In Sheets

       If aa.Shapes.Count > 0 Then

         For Each shp In aa.Shapes

           With shp

             .Placement = xlMove

             .ControlFormat.PrintObject = True

           End With

         Next

       End If

     Next

     End Sub

    ■巨集3:

     Sub 物件屬性設定3()

     Dim aa As Worksheet

     For Each aa In Sheets

       If aa.Shapes.Count > 0 Then

        With aa.DrawingObjects

          .Placement = xlMove

          .PrintObject = True

        End With

       End If

     Next

     End Sub

    ■使用〔巨集2〕及〔巨集3〕即可避免用到〔Select〕,效率較佳。

    ----------------------------------

  • Kubi
    Lv 5
    9 年前

    試試看。

    Sub JJ()

      Dim aa As Worksheet, sh As Worksheet

      Set sh = ActiveSheet

      celladdress = ActiveCell.Address

      For

        '迴圈內程式碼不改

        '...

      Next

      sh.Select

      Range(celladdress).Select

    End Sub

    2011-08-30 07:58:33 補充:

    又是一則笨鳥先飛的案例,一看就知道准大的程式碼更簡潔,因此煩請准大上答囉。

還有問題?馬上發問,尋求解答。