VBA code to set/reset focus to workbook

[jacobrcotton]

Hello,

I have VBA Userform that allows a user to enter a whole bunch of information, maps that information to a spreadsheet and then I get to dig through the collective data. Awesome. Whatever.

My problem is that this Userform doesn't "play well" with other workbooks being open. Specifically, when I open it myself, it will attempt to run the Userform macro through a different open workbook (PERSONAL workbook, or whatever workbook(s) may be open at the time), which of course throws an error.

When it does run while there are other workbooks open, it easily loses its focus when other workbooks are activated/focused/clicked on, and then does not easily refocus on the Userform workbook when it is activated/focused and data is entered.

Any ideas here?

I do have a de-ID'd version of the workbook, so I can post it if necessary.

Thanks!

 

[Norie]

In the userform code are you explicitly referencing the correct workbook when referring to ranges/sheets etc?

 

[Fazza]

maybe something like when the userform initialises

set rngStartingSelection = Selection
or = ActiveCell


then at end of code add some steps to
- reactivate starting workbook rngStartingSelection.parent.parent.activate
- or starting worksheet rngStartingSelection.parent.activate
- or starting cells rngSTartingSelection.activate

or something like that, anyway. untested. cheers

 

[jacobrcotton]

Interestingly enough...I don't think so.

There are two mapping functions in the macro. DATA to USERFORM and USERFORM to DATA.

Sub MapToUserForm(Row As Long)
    
    PlanName = Worksheets("MedPlans").Cells(Row, 4)
    PlanType = Worksheets("MedPlans").Cells(Row, 6)
...
...
...
    ERCostSharePEPM = Worksheets("MedPlans").Cells(Row, 100)


    FormatToDisplay


End Sub

and a similar Sub MapToData(Row as Long).

Now, unfortunately, its possible that the workbook is renamed by different colleagues/offices/divisions across the company. So I will not be able to reference an explicit workbook name, as that name could change.

 

[Norie]

Is the userform located in the same workbook as you want to read/write from?

If it is you can use ThisWorkbook.

Sub MapToUserForm(Row As Long)
Dim wsMedPlans As Worksheet

    Set wsMedPlans = ThisWorkbook.Worksheets("MedPlans")
    
    With wsMedPlans

        PlanName = .Cells(Row, 4)
        PlanType = .Cells(Row, 6)
'...
'...
'...
        ERCostSharePEPM = .Cells(Row, 100)

    End With

    FormatToDisplay


End Sub

 

[jacobrcotton]

That does not seem to solve the issue. It works when no other workbooks are open, but does not work when others are open and I've moved between the workbooks.

It looks like the

Set wsMedPlans = ThisWorkbook.Worksheets("MedPlans")

is getting hung up on the ThisWorkbook piece. It seems that "ThisWorkbook" is focused on the prior workbook, instead of the workbook that launched the Userform, and therefore not finding the worksheet "MedPlans".

 

[Norie]

ThisWorkbook always refers to the workbook the code is in, so if the posted code is in the same workbook as the userform it refers to the workbook the userform is in.

 

[jacobrcotton]

That does not seem to solve the issue. It works when no other workbooks are open, but does not work when others are open and I've moved between the workbooks.

It looks like the

Set wsMedPlans = ThisWorkbook.Worksheets("MedPlans")

is getting hung up on the ThisWorkbook piece. It seems that "ThisWorkbook" is focused on the prior workbook, instead of the workbook that launched the Userform, and therefore not finding the worksheet "MedPlans".

Sorry. To fully answer your question, yes, the read/write is in the same workbook as the userform. But it seems to be looking for those worksheets in prior workbooks.

 

[Norie]

Like I said ThisWorkbook always refers to the workbook the code is in, so based on the sample code you posted what I suggested will refer to a sheet named 'MedPlans' in the same workbook the code is in.

If there is other code, for example in the sub FormatToDisplay, then you would need to change that so it also refers to ThisWorkbook.

PS I'm assuming 'MedPlans' is in the same workbook as the userform.

 

[jacobrcotton]

Norie,

So actually this seems to have worked (like you said), but has exposed another problem which made it look like it had not worked.

Thanks for the help! Now, onto this new problem.