Assigning a Button

[KishorKK]

Hi All,

When i am trying to assign a button in excel taking from shape, i am getting this error?
How to overcome this? Pls explain

 

[Chihiro]

I suspect you are referencing macro in worksheet module. Instead, put your sub/macro in regular module.

If that doesn't fix the issue, upload sample workbook.

 

[KishorKK]

Thanks for the reply, i didn't get can you explain? What regular mode

 

[Chihiro]

Regular module = When you add module in VBE (editor).
Most macro that's referenced by button/shapes should reside here.



Sheet1 = Worksheet Module
When you want to run the code tied to specific cell change, selection change on a given sheet. Code goes here.

ThisWorkbook = Workbook Module
When you want to run the code when workbook is opened, closed... or when you want to run the code on all worksheets when some change is made etc. Code goes here.

 

[KishorKK]

in sheet5 i want to assign macro can you explain with this

 

[Chihiro]

So which macro do you want to assign to it?

 

[KishorKK]

Function_If i want to assing to that marks one

 

[Chihiro]

You can't assign entire module to a button. Only single Sub in a module can be assigned.

 

[KishorKK]

yeah i know!! only one with that i have to generate result, take any sub with the click i have to get result

 

[KishorKK]

pls help me with this

 

[NARAYANK991]

Hi ,

When you click on Assign Macro , within the box , type :

Function_IF.if1

Narayan

 

[KishorKK]

thanks man it worked

 

[NARAYANK991]

Hi ,

The problem is that If1 can also refer to a worksheet cell !

Name your macros with longer names , so that they cannot be taken to refer to a worksheet cell ; for example , instead of If1 , if you rename the same macro to LongerIf1 , then the qualifying Function_IF is not required.

You can directly assign the macro LongerIf1.

Narayan

 

[KishorKK]

Okay. Thanks @Chihiro @NARAYANK991

 

[Chihiro]

What Narayan said.

It's always bad idea to name subs or variables with string/value that can conflict with system.

Typical way to avoid such issue:
1. For variables precede it with "my"
2. For subs "Name_Numeric" should work. Ex: if_1