0
09103042505

ساخت شیت فهرست در اکسل با استفاده از ماکرو

در فایلهای اکسل بزرگ، با تعداد شیتهای فراوان ساخت شیت فهرست امری حیاتی می‌باشد که با استفاده از آن کاربر میتواند به راحتی بین شیتها جابه‌جا شود. شیت فهرست عبارت است از یک شیت (که عموما اولیت شیت است) که در آن اسم تمامی شیتها آورده شده است و کاربر با کلیک کردن بر روی اسم هر شیت وارد آن شیت میشود.

به منظور ساخت فهرست در این شیت، باید از دستور هایپرلینک استفاده نمود که حالت ساده و مقدماتی آن به صورت دستی صورت میگیرد ولی در این آموزش بدین منظور از ماکرو استفاده میکنیم.

 

فرایند پیاده سازی

در گام نخست با زدن کلید Alt+F11 از صفحه کلید، وارد محیط VBE شده و در پنجره سمت چپ قسمت Properties Windows بر روی اسم شیت راست کلیک کرده و گزینه Insert Module را انتخاب نموده تا پنجره ثبت کد در ماژول نمایان شود. سپس کدهای ذیل را در این پنجره وارد نموده.

 

کد VBA

در ابتدای هر دستور، مطابق با تمامی ماکرو ها از عبارت Sub-End Sub به صورت ذیل استفاده میشود.

Sub Fehrest_sheet()

End Sub

سپس سایر دستورات در بین عبارات Sub و End Sub به صورت جداگانه در یک خط می ‌آیند.

 

در خط اول ابتدا متغیر I را به شکل زیر از نوع Long تعریف میکنیم.

Dim i As Long

بعد از تعریف متغیر، در خط بعدی، با استفاده از دستور Sheets.Add یک شیت جدید در ابتدای لیست شیتها ایجاد میکنیم.

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)

 

سپس اسم شیت ایجاد شده را تغییر داده و اسم مد نظر را برای آن لحاظ میکنیم که در این مثال از اسم “فهرست” استفاده شده است که بهتر است این اسم به انگلیسی نوشته شود.

ActiveSheet.Name = “فهرست”

 

 

سپس با استفاده از دستور For به صورت ذیل فرایند ایجاد هایپرلینکسلولها در شیت “فهرست” با سایر شیتها را انجام میدهیم.

For i = 1 To Sheets.Count

Next i

 

حلقه فوق، به تعداد شیتهای موجود تکرار میشود ولی انچه درون این حلقه ها باید اتفاق بیفتد، شامل دو مرحله انتخاب سلول مد نظر و ایجاد هایپرلینک است که با استفاده از دستور ذیل انجام میگیرد.

ActiveSheet.Cells(i, 1).Select

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=””, SubAddress:=”’” & Sheets(i).Name & “’!A1”, TextToDisplay:=Sheets(i).Name

 

لذا کافی است تا دستورات فوق را به صورت یکپارچه در کنار یکدیگر وارد کنیم که نتیجه به صورت کد ذیل خواهد بود.

 

Sub Fehrest_sheet()

Dim i As Long

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)

ActiveSheet.Name = “فهرست”

For i = 1 To Sheets.Count

ActiveSheet.Cells(i, 1).Select

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=””, SubAddress:=”’” & Sheets(i).Name & “’!A1”, TextToDisplay:=Sheets(i).Name

Next i

End Sub

 

روش اجرا

به منظور اجرای دستور فوق، کافی است وارد محیط اکسل شده و کلید Alt+F8 را زده تا نجره اجرای ماکرو باز شود، سپس در این پنجره ماکرو نوشته شده (Fehrest_sheet()) را انتخاب نموده و دکمه Run را برای اجرای آن زده.

با اجرای این ماکرو مشاهده میشود که یک شیت جدید به شیتهای اکسل اضافه میشود به گونه ای که در این شیت، هر سلول به یکی از شیتهای اکسل به همان نام لینک شده است.

 

دیدگاه کاربران
  • ارسلان 28 نوامبر 2021

    سلام
    من ۴۰۰ شیت دارم که لیست شیت ها رو گزفتم، حالا می خوام که از رو همین لیست داده های قسمت هایی از اون شیت ها فراخوانی بشه و جلوی نام شیت ها بیاد.
    از چه تابعی استفاده کنم؟

    • امید معتمدی 18 دسامبر 2021

      ـابع indirect

  • خسرو 26 دسامبر 2020

    سلام
    ممنون بابت این آموزشتان
    من این کد را در اکسل خودم وارد کردم ولی با خطای :
    run-time error 1004
    Application-defined or object-defined error

    و این که وقتی روی کلید Debug کلیک میکنم ، خط زیر هایلایت زرد میشه
    ActiveSheet.Name =”فهرست”

    اسم یکی از شیت های من فهرست هست ، همونی که انتخاب کردم برای باز کردن vb

    روی Sheet18 خودم که با نام فهرست در اکسلم اضافه کردم کلیک راست کردم و گزینه insert و سپس module را انتخاب کردم

    اگه میشد که عکس بفرستم بهتر بود ولی گویا امکان این مورد در سایت وجود نداره
    در پایان باید بگم که من کمی زبان بیسیک بلد هستم (کم) ولی با ماکرو و …. در اکسل اولین کارم هست و چیزی بلد نیستم اگه میشه خیلی ساده راهنماییم کنید

    با سپاس فراوان
    خسرو

    • امید معتمدی 28 دسامبر 2020

      سلام
      شیت فهرستتون را حذف کنید.

  • الهام 20 نوامبر 2018

    Sub Build_Sheet_Navigator_with_Goto_Button()
    ‘ On Error Resume Next
    Application.DisplayAlerts = False
    Application.DisplayAlerts = False

    Call Insert_Navigator_WorkSheet ‘Inset Sheet_Navigator worksheet
    Call DeleteAllShapes ‘first remove pervious Button
    Call Insert_Goto_Home_Button ‘ Insert Buttons
    Call ShapePrint ‘Does not Print Button

    Worksheets(“Sheet_Navigator”).Shapes(“HomeBtn”).Delete
    Worksheets(“Sheet_Navigator”).Range(“A2”).Clear
    Worksheets(“Sheet_Navigator”).Range(“A2”) = “فهرست مطالب”
    Worksheets(“Sheet_Navigator”).Activate

    Application.DisplayAlerts = False
    Application.ScreenUpdating = True

    End Sub
    Private Sub Insert_Goto_Home_Button()

    On Error Resume Next

    For Each sh In Worksheets

    With sh.Shapes.AddShape(msoShapeRectangle, 2, 2, 45, 15)
    .Name = “HomeBtn”
    .Fill.ForeColor.RGB = RGB(255, 0, 0)
    .TextFrame.Characters.Text = “Home”
    .Line.Visible = False
    End With

    sh.Hyperlinks.Add Anchor:=sh.Shapes(“HomeBtn”), Address:=””, SubAddress:=”Sheet_Navigator!A1″, ScreenTip:=”Click Here to go Sheet_Navigator Worksheet”

    Next

    End Sub
    Private Sub Insert_Navigator_WorkSheet()

    On Error Resume Next

    Worksheets(“Sheet_Navigator”).Delete
    Worksheets.Add(Sheets(1)).Name = “Sheet_Navigator”

    For Each sh In Worksheets
    i = i + 1
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 1, 1), Address:=””, SubAddress:=”‘” & sh.Name & “‘” & “!A1”, TextToDisplay:=sh.Name
    Next

    With Columns(“A:A”)
    .EntireColumn.AutoFit
    HorizontalAlignment = xlLeft
    End With

    End Sub
    Private Sub DeleteAllShapes()

    On Error Resume Next

    For Each sh In Worksheets
    sh.Shapes(“HomeBtn”).Delete
    Next

    End Sub
    Private Sub ShapePrint()

    ‘This Procedure set button print property to false which Button does not print

    On Error Resume Next
    Application.ScreenUpdating = False

    For Each sh In Worksheets
    sh.Activate
    sh.Shapes(“HomeBtn”).Select
    Selection.PrintObject = False
    sh.Cells(1, 1).Activate
    Next

    End Sub

  • مصطفی اسدی 26 اکتبر 2018

    سلام دوست عزیز
    ممنون از راهنماییتون چطور به هر شیت کلید هوم یافهرست رو اضافه کنم
    ممنون از راهنماییتون

    • امید معتمدی 30 اکتبر 2018

      سلام
      منظور از کلید Home چی هست؟

  • کورش 7 فوریه 2018

    سلام.مهندس خداقوت
    من تعداد بسیار زیادی اسم کالا در اکسل دارم که میخوام این اسامی به صورت اتوماتیک نام شیت های اکسل شوند.چون تعداد زیاد این اسامی و تعداد زیاد شیت ها عملا کار دستی و یکی یکی کردن ان را وقت گیر میکنه.
    لذا خواهش مندم که اگر راه حلی داره به ایمیلم پیام بدید یا موقع جواب لینک این صفحه برام ایمیل کنید

    • امید معتمدی 10 فوریه 2018

      سلام
      تنها راه استفاده از vba است
      مثلا اگر ۱۰ تا اسم کالا در ستون A دارید کد ذیل میتونه کمکت کنه
      Sub z()

      For i = 1 To 10
      Sheets.Add
      ActiveSheet.Name = Cells(i, 1)
      Next i

      End Sub

      • فرهاد 14 ژوئن 2023

        متاسفانه خطا میده. روی خط
        Activesheet.name=cells(i,1)

  • ety 2 جولای 2017

    ممنون از سایت خوبتون.
    موقع اجرای این برنامه پیغام خطای Run time error 424 با پیام object required میدهد. دلیلش چیه . ؟

    • ety 2 جولای 2017

      البته روی خط آخر برنامه قبل از Next این پیام را میدهد .

      • Omid Motamedi 2 جولای 2017

        برای بررسی باید کدتون را ببینم

        • فرشته 29 ژوئن 2020

          سلام. من هم همین مشکل رو دارم. کد همونی هست که خودتون در سایت قرار دادین.

          • امید معتمدی 8 جولای 2020

            سلام
            ممکنه نتیجه را برایم بفرستید

    • Omid Motamedi 2 جولای 2017

      سلام
      این خطا در هنگام استفاده از کد vba در زمانی که به خط کدی نامعقول میرسه نمایش داده میشه برای مثال اگر در سلول a1 باشیم و به اکسل بگیم یک سلول به سمت بالا حرکت کنه، به دلیل نبودن سلول، خطا خواهد داد. لذا این پیغام کاملا به نوع دستور و نوع اجرا بستگی داره و طی حالات مختلف ممکنه رخ بده

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این سایت از اکیسمت برای کاهش هرزنامه استفاده می کند. بیاموزید که چگونه اطلاعات دیدگاه های شما پردازش می‌شوند.