ساخت فرم ورود اطلاعات در اکسل

اکسل می تواند محلی برای ذخیره اطلاعات و داده‌های سازمان شما باشد و البته در بسیاری از موارد برای ذخیره داده ها در یک شیت اکسل شاید ترجیح دهید که ابتدا اطلاعات توسط کاربر تایپ شود و سپس با زدن یک دکمه آن اطلاعات در یک شیت اکسل ذخیره شوند.

دلایل استفاده از فرم ورود اطلاعات در اکسل:

1- نحوه چیدمان دلخواه فرم

2- بررسی کردن اطلاعات توسط گزینه هایی مانند Data Validation

3- ثبت اطلاعات به صورت یک لیست (جدول) در اکسل برای گزارش گیری

اشتباه عمده کاربران اکسل در طراحی فرم

بسیاری از کاربران اکسل برای ساخت فرم "ورود اطلاعات" از user formها در VBA استفاده می کنند که به نظر نگارنده این انتخاب الزاما بهترین نیست و البته استفاده از یک شیت اکسل به مراتب می‌تواند گزینه بهتری باشد.

 

فرم user form excel vba

(یک نمونه فرم ساخته شده در محیط VBA اکسل)

 

استفاده از userform کار را به مراتب سخت و پیچیده می کند:

1- فرم های VBA به مراتب به کد نویسی بیشتری احتیاج دارند. مثلا برای ساخت یک کمبو باکس .

2- از دست دادن ده ها امکانات اکسل مانند فرمول نویسی اضافی / تست داده ها با توابع اکسل/ onditional formatting

بنابراین توصیه می شود که در صورتی که دلیل محکمی برای استفاده از userform های VBA اکسل ندارید، فرم خود را در اکسل بسازید.

اصول کلی ساخت فرم ورود اطلاعات در اکسل

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

فرض کنید که قرار است اطلاعت فرم بالا را در لیست زیر کپی کنید:

فرم user form excel vba

توجه : در فایل پیوست این مثال کاملا پیاده سازی شده است.

1- آنچه را که می خواهید کپی کنید ابتدا به صورتی ساده با فرمول نویسی در یک سطر در کنار هم بیاروید تا کار کپی آنها ساده شود.

2- محل سلول خالی در لیست مرجع را که قرار است اطلاعات را در آنجا کپی کنید، با فرمول COUNTA بیابید.

3- از کد VBA که در فایل پیوست مشاهده می کنید استفاده کنید تا اطلاعات را در شیت مرجع PASTEVALUE نمایید.

به روزرسانی مقاله (فرودین 97)

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

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


محافظت از داده‌های اصلی

یکی از مزایای ساخت فرم ورود اطلاعات در اکسل آن است که داده‌های اصلی (دیتابیس) ما توسط کاربر در دسترس نباشد و کاربر فقط بتواند ثبت را انجام دهد.

برای فهم این موضوع از یک مثال واقعی که در یکی از دوره‌های فرساران مطرح شد استفاده می‌کنم:

در یک کارخانه سرپرست خط تولید می‌خواهد که چند تا از اپراتورهایش داده‌هایی را ثبت کنند اما ممکن است یکی از آنها به اشتباه داده‌های قبلی را دستکاری کند.

برای اینکار کافی است که قبل از ثبت داده‌ها در برنامه، پسورد شیت داده‌های اصلی برداشته شود و سپس داده‌ها ثبت شوند و سپس پسورد بر روی شیت مجدد فعال شود. این کار توسط کد زیر انجام می‌شود. 

نکته: پسورد شیت دیتابیس اصلی ما (که نامش آن List است) را salam قرار داده شده است و باید در هنگام ذخیره کردن فایل این شیت را با این پسورد protect کنیم.

Sub Copy2List()

    Sheets("List").Unprotect "salam"

    x = Sheets("Form").Range("L1").Value

    Sheets("Form").Range("M1:S1").Copy
   
    Sheets("List").Cells(x, 1).PasteSpecial xlPasteValues
   
    Sheets("List").Protect "salam"
    
End Sub
 

شما هم تجربه یا دیدگاه خود را بنویسید:

محتوای این فیلد خصوصی است و به صورت عمومی نشان داده نخواهد شد.

متن ساده

  • تگ‌های HTML مجاز نیستند.
  • خطوط و پاراگراف‌ها بطور خودکار اعمال می‌شوند.
کد امنیتی
احمد زندی (تایید نشده) در تاریخ سه شنبه, 1397/10/18 - 22:34 نوشته:

با سلام و احترام
سپاس از مطالب مفید و کاربردی
اگر امکان داره براتون فیلم های آموزشی و برنامه نویسی و ایجاد ماکروها ، را بفرمایید به چه نحوی تهیه کنیم
با تشکر

فرشید میدانی در تاریخ پنجشنبه, 1397/10/20 - 10:29 نوشته:

می‌توانید کتاب زیر را از قسمت محصولات تهیه فرمایید:

کتاب برنامه نویسی VBA در اکسل

شاملو (تایید نشده) در تاریخ چهارشنبه, 1397/07/04 - 13:51 نوشته:

استاد این برنامتون ویروسیه!!!

فرشید میدانی در تاریخ پنجشنبه, 1397/10/20 - 10:30 نوشته:

نه فایل مشکلی نداره اما گاهی آنتی ویروس‌ها اینکار رو با فایل های اکسل انجام می دهند!

توحیدی نژاد (تایید نشده) در تاریخ یکشنبه, 1397/04/24 - 09:06 نوشته:

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

فرشید میدانی در تاریخ شنبه, 1397/05/27 - 01:05 نوشته:

واقعیت این است که اکسل برای این کارهای طراحی نشده است و اکسس گزینه بسیار مناسب تری برای سوال شما است.

m78167.3 در تاریخ یکشنبه, 1397/02/23 - 22:19 نوشته:

باسلام.ایا بین دوقسمت این جمله (PasteSpecial xlPasteValues)فاصله باید باشه؟
در مورد این جمله کمی توضیح میفرمایید

m78167.3 در تاریخ یکشنبه, 1397/02/23 - 13:10 نوشته:

با سلام وتشکر بابت مطالب مفید سایت وکتابتون .من بخاطر شغلم باید یک لیست را در چند جدول جداگانه بطوری که دو جدول با 25 سطر در یک کاغذ a4 ثبت کنم به کمک مطالب کتاب وسایت کارهایی رو انجام دادم که با یک فرم میتونم مطالبو ثبت کنم ولی نمیتونم تنظیم کنم که بعد از تمام شدن جدول اول یعنی 25 سطر داده ها در جدول دوم ثبت بشن لطف کنید ودر این مورد منو راهنمایی کنید با تشکر

علیرض (تایید نشده) در تاریخ دوشنبه, 1397/01/20 - 22:13 نوشته:

سلام
خیلی ممنون بابت این مطلب قابل توجه و کارآمد
یه سوال خدمتتون داشتم
کلید ثبت تماس رو چگونه فرمول نویسی کردید ممنون

فرشید میدانی در تاریخ یکشنبه, 1397/01/26 - 21:20 نوشته:

بسیار ساده است :)
کافی است که یک شکل / یک عکس و .. را در شیت قرار دهید و سپس روی آن R-Click کنید و گزینه Assign Macro را بزنید و ماکروی مورد نظرتان را انتخاب کنید . از این به بعد با کلیک کردن بر روی این شکل/تصویر، ماکرو اجرا می شود.

علیرضافرزان (تایید نشده) در تاریخ چهارشنبه, 1397/01/01 - 13:33 نوشته:

سلام خدمت شما من هنگام اجرای کد بالا خطای ۱۴۰۰۱ در برنامه دارم میشه راهنمایی کنید

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:07 نوشته:

آیا دقیقا در همین فایل که از اینجا دانلود کرده اید، خطا میدهد؟
اگر از این کدها در یک فایل دیگری استفاده می کنید، دقت داشته باشید که نام شیت ها دقیقا باید همین شیت های فایل اصلی باشد.

نحوی (تایید نشده) در تاریخ پنجشنبه, 1396/10/21 - 12:21 نوشته:

عالیییییییییییییییییییییییییییییییییییییییییییییییییییییی
خیلی خوب بود
لطفا باز هم از این ترفند ها و نکات بگذارید

مصطفی (تایید نشده) در تاریخ پنجشنبه, 1396/08/25 - 18:13 نوشته:

با تشکر مجدد و فراوان از آمزشهای شما
ارتباط بین ماکرو و ماژول چطور باید ایجاد بشه؟
ممنونم

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:33 نوشته:

سوال شما کمی اشتباه است.
این دو چیز رابطه سلسله مراتبی دارند و همیشه به هم مربوط هستند.
ماژول یعنی یک صفحه خالی (مثل صفحه پاورپوینت) که در آنجا برنامه (ماکرو) را تایپ می کنیم.

عیسی سعیدی (تایید نشده) در تاریخ جمعه, 1396/07/28 - 09:30 نوشته:

سلام

اگر بخوایم از این فرم برای اینکه در دو شیت جداگانه ذخیره کنیم چکار کنیم

مثلا اکر دختر باشد تو شیت دختران و اگر پسر باشد در شیت پسران

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:36 نوشته:

ساده است . با دستور IF:

   If Range("A1") = "Female" Then

        Sheets("ListFemale").Cells(x, 1).PasteSpecial xlPasteValues

   ElseIf Range("A1") = "Male" Then

        Sheets("ListMale").Cells(x, 1).PasteSpecial xlPasteValues

   End If

مقصودی (تایید نشده) در تاریخ چهارشنبه, 1396/01/09 - 13:11 نوشته:

سلام

میشه بگین چطور میتوان یه دکمه پرینت نیز کنار ثبت تماس اضافه کرد؟

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:30 نوشته:

خیلی ساده است. لطفا مقاله های آموزش رکورد ماکرو را ببینید.

sinaem (تایید نشده) در تاریخ یکشنبه, 1395/07/04 - 11:24 نوشته:

سلام

 

چطور میتونیم اطلاعات رو به جای یک شیت به چند شیت ارسال کنیم؟

به این صورت که نام شیت های مربوطه رو درون یه کمبوباکس داشته باشیم و در هنگام ورود اطلاعات نام شیت مربوطه انتخاب و اطلاعات پس از ثبت به اون شیت ارسال بشه.

 

متشکرم.

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:29 نوشته:

اینکار به سادگی شدنی است. فرض کنید در یک سلول مثلا A1 نام شیت مقصد (مربوطه) را تایپ می کنیم. بنابراین کد ما اینگونه خواهد شد:

Sheets(A1).Cells(x, 1).PasteSpecial xlPasteValues

m.r.c در تاریخ یکشنبه, 1394/09/08 - 15:08 نوشته:

با سلام 

مطالب خوب و جامع و کاربردی دارین . متشکرم

یه سوال دارم و میخوام درموردقسمت :

محل سلول خالی در لیست مرجع را که قرار است اطلاعات را در آنجا کپی کنید، با فرمول COUNTA بیابید رابیشترتوضیح بدید .

و اینکه این عدد 6 در سلول قرمز رنگ دقیقا چه چیزی به ما نشون می ده و 2+ بعد فرمول برای چیه ؟

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:26 نوشته:

ما باید با یک روشی بفهمیم که اولین سطر (سلول) خالی در شیت اطلاعات کجاست. با تابع counta ، سلولهای پُر را می شماریم و سپس چون اولین سطر در این فایل خالی است پس باید 1 واحد به این شمارش اضافه شود و برای یافتن اولین سلول خالی هم 1 واحد دیگر اضافه شود.
بنابراین تعداد سلولهای پُر را با عدد 2 جمع میزنیم.

Siavash (تایید نشده) در تاریخ پنجشنبه, 1394/06/12 - 19:47 نوشته:

سلام.

اون دکمه ثبت و بقیه دکمه ها رو چطور می شه آماده کرد؟

 

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:23 نوشته:

خیلی ساده است، روی یک عکس یا یک شکل و .. R-Click کنید و گزینه Assign Macro را بزنید.
از این پس با کلیک بر روی آن شکل و یا عکس، آن ماکرو اجرا خواهد شد.

سید عدنان حسینی (تایید نشده) در تاریخ سه شنبه, 1393/11/28 - 18:31 نوشته:

سلام

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

فرشید میدانی در تاریخ چهارشنبه, 1397/01/01 - 20:08 نوشته:

آیا ماکروها فعال هستید؟
مقاله فعال کردن ماکرو ها را درابتدا بخوانید.

محمد (تایید نشده) در تاریخ پنجشنبه, 1393/10/11 - 18:13 نوشته:

من دو سه سالی است که با این سایت آشنام.واقعاً استاد میدانی مطالب بسیار جالب و آموزنده ای رو ارائه می دهند.امیدوارم همواره پیروز و سربلند باشند

علی افشاری (تایید نشده) در تاریخ چهارشنبه, 1393/10/10 - 16:55 نوشته:

با سلام 

بسیار ساده و کاربردی بود مهندس جان

عالی

مهدب (تایید نشده) در تاریخ سه شنبه, 1393/10/09 - 10:26 نوشته:

باسلام

بسیار ساده - کاربردی و با جزییات مطرح شده است

ممنون . هر بار از سایت بازدید می کنم نکته ای جدید و کاربردی می آموزم

موفق باشید و سالم