VBA allowing user to select range in another open workbook

[quinleroux]

Hi All,

Forum nubie here.

Was wondering if someone could help with a sollution of how to allow the user to select a range of cells in another open workbook?

I have a register that gets information from various sources... it would be really helpful if the user could click a button, select a range of cells from any open workbook so that the macro in the register could deal with that information as required.

Many thanks in advance.

 

[sn281]

Hi,

I think this is quite possible although I'm struggling to understand what you are asking for exactly.

If you could clarify this, I'll have a go.

sn281

 

[quinleroux]

Thanks sn281!

The clarification:

Prior to the user opening up a SOW Register from a network folder, he would have run and saved an Excel report from SAP (SAP Report).

The information from SAP Report must be pasted in a very specific way (values) and place (sheet and grid reference) into the SOW Register in order for other macros to handle the information correctly and thus update the SOW Register accordingly. In lieu of this, I've written a macro to handle the copy and paste.

So when the user has both the SAP Report and the SOW Register open, he will click a button in the SOW Register which will prompt him to select the required cells in the SAP Report. This is where I struggle... I don't know how to use the application.inputbox method in a way that it allows the user to navigate to another open workbook (SAP Report) to select the required information.

(The work around I'm currently using is an elaborate set of instructions to the user to open both files and ensure that they can see the data they will need to copy (View>Arrange All>Verticle))

Many thanks again for your time and response.

 

[sn281]

Ah I see what you mean. Could you not set the input box value as a variable, then get the macro to run that variable on the other workbook? Would this work?

 

[quinleroux]

If (and that is a big "if") I understand what you are suggesting, that won't work because I have no control over what, where or how the user will save the SAP Report as it is a new file created by SAP on an export. Therefore I won't be able to assign the variable.

 

[lancerj017]

you need to use userform and refconfig to manipulate the range in other open workbook. search refconfig for how to use it.

 

[quinleroux]

Hi lancerj017. Thanks for this...please could you point me in the right direction with a link or something. Tried searching "refconfig" here and from Google, a number of different ways, and kept coming up with everything from free downloads to "Did you mean "defconfig"".

Again, my thanks for your time.