Loncat ke daftar isi utama

Bagaimana cara memicu atau menjalankan Makro dengan mengklik sel tertentu di Excel?

Saat bekerja dengan Microsoft Excel, Anda mungkin tahu cara menjalankan Makro tertentu dengan Tombol Perintah. Tapi tahukah Anda cara menjalankan Makro hanya dengan mengklik sel tertentu di lembar kerja? Artikel ini akan menunjukkan kepada Anda metode memicu Makro dengan mengklik sel tertentu secara detail.

Picu atau jalankan Makro dengan mengklik sel tertentu dengan kode VBA


Picu atau jalankan Makro dengan mengklik sel tertentu dengan kode VBA

Kode VBA berikut dapat membantu Anda menjalankan Makro dengan mengklik sel tertentu di Excel. Silakan lakukan sebagai berikut.

1. Pada lembar kerja dengan sel yang perlu Anda klik untuk menjalankan Makro, klik kanan tab lembar, lalu klik Lihat kode dari menu konteks

2. Dalam Microsoft Visual Basic untuk Aplikasi jendela, salin dan tempel skrip VBA di bawah ini ke jendela Kode.

Kode VBA: Memicu atau menjalankan Makro dengan mengklik sel tertentu

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            Call MyMacro
        End If
    End If
End Sub

Catatan:

1. Dalam kode, D4 ​​adalah sel yang akan Anda klik untuk menjalankan Makro;

2. Harap ganti nama kode Makro Saya dengan Makro Anda akan menjalankan di lembar kerja. Lihat tangkapan layar:

3. tekan lain + Q tombol secara bersamaan untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, saat mengklik sel D4 di lembar kerja saat ini, Makro yang Anda tentukan akan segera dipicu.


Terkait artikel:

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (37)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hola,

No encuentro la manera de hacer lo que necesito. Espero de veras que me podáis ayudar.
Necesito que se lance una MACRO al hacer clic en una celda, pero no sé en qué celda va a ser, no lo puedo comparar con (si se ha seleccionado la celda A3, por ejemplo, que se lance la acción) porque por funcionalidades del fichero, las celdas se van a mover de sitio (se han podido insertar, mas o menos filas encima). Necesitaría poder saber qué celda es la que se ha seleccionado.
Hay alguna manera de poder hacer eso?
Muchas gracias de antemano.
This comment was minimized by the moderator on the site
Hi Hola,
The cell address is constant. All you can move is the value of the cell.
The cell you specified in the VBA code is still the cell that trigger the specified VBA code.
This comment was minimized by the moderator on the site
I didn’t have any expectations concerning that title, but the more I was astonished. The author did a great job. I spent a few minutes reading and checking the facts. Everything is very clear and understandable. I like posts that fill in your knowledge gaps. This one is of the sort.
This comment was minimized by the moderator on the site
Grandioso! Grazie, era una vita che sognavo di poterlo fare, se può essere utile a qualcuno avendo necessità di eseguire più codici su più celle per
aumentare il numero di opzioni basta aggiungere le nuove istruzionisempre sotto la stessa option explicit altrimente ci viene detto che il riferimento al
worksheet non è univoco. Mi spiego meglio evidentemente su come ho adattato il codice con il mio esempio;

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("Y64")) Is Nothing Then
Range("Y65:Y78").Select
Range("Y65").Activate
Selection.ClearContents
Range("Y65").Select
End If
If Not Intersect(Target, Range("A33")) Is Nothing Then
Range("A33").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
SendKeys "^v"
SendKeys "{BACKSPACE}"
End If
End If
End Sub

Grazie ancora è stata un'autentica meraviglia!

Massimo
This comment was minimized by the moderator on the site
For those wanting this to work for merged cells, the quickest and dirtiest way is to update Line 2 as follows:
<div data-tag="code">Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 0 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
Call MyMacro
End If
End If
End SubSelection.Count is being used to determine if a selection has been made before executing the remaining macro. If your selection consists of four cells that have been merged, Selection.Count will equal 4, and the remaining code will never execute.You can also change line 2 to the exact number of cells that were merged, but that will cause problems if you merge additional cells.
This comment was minimized by the moderator on the site
It is important to stress that the macro only runs in the worksheet.
Those of us who use the regular visual basic editor often end up storing macros in other places than in the current worksheet.
None of these codes will work in that situation.
I find this code to be much simpler and it works very well for me: 

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("d1"), Target) Is Nothing Then
Call aa
End If
End Sub
Sub aa() ' put your code here
Range("D1").Select
Selection.Copy
Range("F1").Select
ActiveSheet.PasteEnd Sub
John Wells
This comment was minimized by the moderator on the site
Hi John Wells,Thank you for sharing.
This comment was minimized by the moderator on the site
Does not work anymore.
Worksheet_SelectionChange does not exist by default. Meaning you cannot use that to trigger a macro.

The only embeded sub you can use are referenced in "Workbook" dropdown list, so...

The only closest thing you can do is using Workbook_SheetBeforeDoubleClick. But is will enter Edit mode (still not a big deal).
This comment was minimized by the moderator on the site
Hi CodeKiller.Worksheet_SelectionChange only exists in the Sheet(Code) editor. Right click the sheet tab and click View Code to activate the Sheet(Code) editor.
This comment was minimized by the moderator on the site
Hello, Thank you for your interesting subject. What if for example I want to to click on D4 in Sheet1 and see the result of Macro in Sheet2!F3.
This comment was minimized by the moderator on the site
hello, I was trying to use this code to Run a paste macro but can't seem to figure out how to paste the macro properly.

here is my original Marco

Sub Paste()
'
' Paste Macro
'

'
Range("B34").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
End Sub


any help is appreciated
This comment was minimized by the moderator on the site
Thanks for this code. Is it possible to get this to work by clicking on a cell that is merged with others?
This comment was minimized by the moderator on the site
Hi M.Symonds,
The code in this article can do you a favor: https://www.extendoffice.com/documents/excel/4354-excel-click-on-cell-to-run-macro.html
Thank you for your comment.
This comment was minimized by the moderator on the site
Thanks, but how about several cells for clicking for running several macros.
This comment was minimized by the moderator on the site
Hi,
Try the below VBA code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRgArr As Variant
Dim xFunArr As Variant
Dim xFNum As Integer
Dim xStr As String
Dim xRg As Range
xRgArr = Array("A1", "D1", "C1") 'Cells used to trigger macro
xFunArr = Array("Code name1", "Code name2", "Code name3") 'The corresponding code names
If Selection.Count = 1 Then
For xFNum = 0 To UBound(xRgArr)
Set xRg = ActiveSheet.Range(xRgArr(xFNum))
If Not Intersect(Target, xRg) Is Nothing Then
xStr = xFunArr(xFNum)
Application.Run xStr
End If
Next
End If
End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations