PLEASE HELP : VBA CODE ISSUE WITH PROMPTING AN ACTIVE X CONTROL BOX

[DAGUET]

Hello. i already came here in this forum and was amply satisfied by the reactivity of its members to answer to me and solve my problem. today I have another "trivial" problem: I have build an xl spreadsheet with a vba code attached to it. This code inter alia commands the prompting of an dialog box (actually a formular). This formular appears upon clicking on an active X box I created and I wired to the code. But for unknown reason (and I really tried to see where it could come from but I could not...) when I click on this box, instead of prompting the formular, it shows an error message saying that the macro command may not be avaialble in the worksheet or that macros may be desactivated (not true). Do you have an idea where this can come from and help me? your help appreciated (file attached).
Best Regards

 

[NoSparks]

Right click on the command button => select assign macro => hit delete to eliminate 'DCF AND COMP VBA.xlsm'!CommandButton1_Click from the displayed Macro Name: => click OK

Right click on the command button again => CommandButton1_Click will be showing in the Macro Name: => click NEW and put DCF1.show into the procedure that comes up.

Now when you click on the command button you will get Run-time error '424' Object required. Click Debug, the DCF1.Show will be highlighted, use F8 to step one line at a time through the code to see where the errors are.

In this case you are trying to initialize textboxes on the user form that are not named what you think they are.

 

[NARAYANK991]

Hi Patrick ,

I may be wrong but I think the captions that you have assigned , have been assigned wrongly ; some of the captions have been assigned to the labels , when they should be assigned to the textboxes on the userform.

Secondly , Excel is an intelligent program , but not so intelligent that it can understand your style of labeling ; in different places , the following have been used :

LastHistoricalYear

LastHistoricalYearComboBox

LastHistoricalYearBox

LHYCombobox

This will never work. You need to have one name to refer to the ComboBox in every statement that you use to access the ComboBox.

Narayan

 

[DAGUET]

Right click on the command button => select assign macro => hit delete to eliminate 'DCF AND COMP VBA.xlsm'!CommandButton1_Click from the displayed Macro Name: => click OK

Right click on the command button again => CommandButton1_Click will be showing in the Macro Name: => click NEW and put DCF1.show into the procedure that comes up.

Now when you click on the command button you will get Run-time error '424' Object required. Click Debug, the DCF1.Show will be highlighted, use F8 to step one line at a time through the code to see where the errors are.

In this case you are trying to initialize textboxes on the user form that are not named what you think they are.

Dear NoSparks

I followed scrupulously your procedure but don't see where it can drive me to. Indeed, i have error 424 showing up but this does not solve my problem of not being able to prompt the formular user form that was coded.

maybe I missed something here.....


Best Regards

 

[DAGUET]

Hi Patrick ,

I may be wrong but I think the captions that you have assigned , have been assigned wrongly ; some of the captions have been assigned to the labels , when they should be assigned to the textboxes on the userform.

Secondly , Excel is an intelligent program , but not so intelligent that it can understand your style of labeling ; in different places , the following have been used :

LastHistoricalYear

LastHistoricalYearComboBox

LastHistoricalYearBox

LHYCombobox

This will never work. You need to have one name to refer to the ComboBox in every statement that you use to access the ComboBox.

Narayan

Dear Narayan

if I follow your reasoning, the fact that the captions have been assigned to the labels rather than the textboxes explain why when clicking on the command button, the formular does not show up? that is right?

Regards

 

[NARAYANK991]

Hi Patrick ,

A few corrections have been made ; can you go through everything and make the other changes that have to be made ?

Narayan

 

[DAGUET]

Hi Patrick ,

A few corrections have been made ; can you go through everything and make the other changes that have to be made ?

Narayan

Dear Narayan (EXCEL NINJA) I saw what you did and now understand better what you were meaning. and it works!!!! Thanks a lot. All my gratitude for this and your proactive action!!

Kind Regards

 

[NoSparks]

maybe I missed something here.....

Yes, the F8 part which would have led you to your mistakes.