مهندسین اکسل اکسل، داشبورد مدیریتی در اکسل، فرمول نویسی در اکسل، توابع اکسل
در فایلهای اکسل بزرگ، با تعداد شیتهای فراوان ساخت شیت فهرست امری حیاتی می‌باشد که با استفاده از آن کاربر میتواند به راحتی بین شیتها جابه‌جا شود. شیت فهرست عبارت است از یک شیت (که عموما اولیت شیت است) که در آن اسم تمامی شیتها آورده شده است و کاربر با کلیک کردن بر روی اسم هر شیت وارد آن شیت میشود.
به منظور ساخت فهرست در این شیت، باید از دستور هایپرلینک استفاده نمود که حالت ساده و مقدماتی آن به صورت دستی صورت میگیرد ولی در این آموزش بدین منظور از ماکرو استفاده میکنیم.
فرایند پیاده سازی
در گام نخست با زدن کلید 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 را برای اجرای آن زده.
با اجرای این ماکرو مشاهده میشود که یک شیت جدید به شیتهای اکسل اضافه میشود به گونه ای که در این شیت، هر سلول به یکی از شیتهای اکسل به همان نام لینک شده است.
سلام
من ۴۰۰ شیت دارم که لیست شیت ها رو گزفتم، حالا می خوام که از رو همین لیست داده های قسمت هایی از اون شیت ها فراخوانی بشه و جلوی نام شیت ها بیاد.
از چه تابعی استفاده کنم؟
ـابع indirect
سلام
ممنون بابت این آموزشتان
من این کد را در اکسل خودم وارد کردم ولی با خطای :
run-time error 1004
Application-defined or object-defined error
و این که وقتی روی کلید Debug کلیک میکنم ، خط زیر هایلایت زرد میشه
ActiveSheet.Name =”فهرست”
اسم یکی از شیت های من فهرست هست ، همونی که انتخاب کردم برای باز کردن vb
روی Sheet18 خودم که با نام فهرست در اکسلم اضافه کردم کلیک راست کردم و گزینه insert و سپس module را انتخاب کردم
اگه میشد که عکس بفرستم بهتر بود ولی گویا امکان این مورد در سایت وجود نداره
در پایان باید بگم که من کمی زبان بیسیک بلد هستم (کم) ولی با ماکرو و …. در اکسل اولین کارم هست و چیزی بلد نیستم اگه میشه خیلی ساده راهنماییم کنید
با سپاس فراوان
خسرو
سلام
شیت فهرستتون را حذف کنید.
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
سلام دوست عزیز
ممنون از راهنماییتون چطور به هر شیت کلید هوم یافهرست رو اضافه کنم
ممنون از راهنماییتون
سلام
منظور از کلید Home چی هست؟
سلام.مهندس خداقوت
من تعداد بسیار زیادی اسم کالا در اکسل دارم که میخوام این اسامی به صورت اتوماتیک نام شیت های اکسل شوند.چون تعداد زیاد این اسامی و تعداد زیاد شیت ها عملا کار دستی و یکی یکی کردن ان را وقت گیر میکنه.
لذا خواهش مندم که اگر راه حلی داره به ایمیلم پیام بدید یا موقع جواب لینک این صفحه برام ایمیل کنید
سلام
تنها راه استفاده از vba است
مثلا اگر ۱۰ تا اسم کالا در ستون A دارید کد ذیل میتونه کمکت کنه
Sub z()
For i = 1 To 10
Sheets.Add
ActiveSheet.Name = Cells(i, 1)
Next i
End Sub
متاسفانه خطا میده. روی خط
Activesheet.name=cells(i,1)
ممنون از سایت خوبتون.
موقع اجرای این برنامه پیغام خطای Run time error 424 با پیام object required میدهد. دلیلش چیه . ؟
البته روی خط آخر برنامه قبل از Next این پیام را میدهد .
برای بررسی باید کدتون را ببینم
سلام. من هم همین مشکل رو دارم. کد همونی هست که خودتون در سایت قرار دادین.
سلام
ممکنه نتیجه را برایم بفرستید
سلام
این خطا در هنگام استفاده از کد vba در زمانی که به خط کدی نامعقول میرسه نمایش داده میشه برای مثال اگر در سلول a1 باشیم و به اکسل بگیم یک سلول به سمت بالا حرکت کنه، به دلیل نبودن سلول، خطا خواهد داد. لذا این پیغام کاملا به نوع دستور و نوع اجرا بستگی داره و طی حالات مختلف ممکنه رخ بده