How to make a macro that changes the parameters of a certain macro shortcut/hotkey?

[kjy1989]

So i have a macro here designated on shortcut CTRL+E.

When the user presses CTRL+E, certain values will enter in the cell ACTIVECELL.Offset(0, 4). But sometimes there are instances where i want it to be entered on ACTIVECELL.Offset(0,5) or (0,3) depending on the user.


From what I heard there's a macro to edit a macro so i can edit the activecell.offset value as i wish, but they recommended not to do so. So as a workaround i will have plenty of Macros ready and the user just has to set the parameters.

so in my code there will be:

Please Login or Register  to view this content.

So how can make it that the user can edit his shortcut of CTRL+E to do either option1, option2, option3?

[HaHoBe]

Hi, kjy1989,

depending on the user.

Really dependigng on the user and always or temportarily? Can you narrow the problem down to be dependent on the column in which the action should be taken?

Please Login or Register  to view this content.

Ciao,
Holger

[kjy1989]

Hi,

I will try my best to narrow it down so you can understand. I will do so by explaining the purpose of this macro. See this excel file our employees are using is designed so they will be faster in making their price quotations for our customers. And depending on the employee, they have different methods on how to make quotation. So for example that CTRL+E shortcut consists of a vlookup on a database that brings out a price to a designated cell, that cell designation dependent on how the employee does his quotation. So what i like is that the user can choose which cell to put his price whether it's located at Activecell.offset(0,2) , (0,3) or (0,4).

So what im aiming for is something like a control panel window of some sort for the users:

for example: Pressing ctrl+shift+e will open up a userform named "SET PARAMETERS".
In here, a window will pop up asking him where he wants to designate his price column, he can choose either column B,C, or D. Once he chooses a column, the corresponding shortcut of CTRL+E changes to the corresponding macro (whether its option1, option2, or option 3).

Of course, im not asking for the entire userform of the parameters, im just asking for a code that will make a certain hotkey change its macro designation. Like how to change ctrl+e to designate to the macro "option2" instead of "option1"

I hope you understand as i have difficulties in explaining my situation because i find this very complicated to my standards. (im a vba beginner)

[nilem]

Hi kjy1989,
maybe something like this

Please Login or Register  to view this content.

[kjy1989]

Ok, im trying it right now, im having a hard time understanding the application.inputbox line...

I will let you know...