Activate any open workbook vba

gtd526

Active Member
Joined
Jul 30, 2013
Messages
332
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I want to activate an Open workbook, regardless of the name. I have macros in a custom ribbon (excel), which can be run on any open workbook.
I have a macro that will convert formula to absolute columns only, which works, but I need to activate the workbook.
I have a UserForm that allows me to 'select' the cells to convert. For the Macro to work correctly, I need to Activate that workbook.
Currently I'm using Wildcards to Activate it (if it starts with "N", using 3 characters), but Workbook Names will differ.
Using Code:
For Each wb In Application.Workbooks
If wb.Name Like "N??" Then ' "*Final*" Like *Final*
wb.Activate
Exit For
End If
Next wb

thanks
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
If you only have one open workbook, "ActiveWorkbook" should suffice (but actually you don't even need that if there is only one open workbook, it will just assume the one).
If you have multiple workbooks open, how is to be determined which one the code should run against?
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
332
Office Version
  1. 2019
Platform
  1. Windows
If you only have one open workbook, "ActiveWorkbook" should suffice (but actually you don't even need that if there is only one open workbook, it will just assume the one).
If you have multiple workbooks open, how is to be determined which one the code should run against?
I could have several workbooks open. It's determined by the 'selection' of cells to convert (I assume). Selection comes from the UserForm. Here is the code:
Private Sub CommandButton1_Click()

Dim SelectRange As Range
Dim Address1 As String
Dim wb As Workbook

'Workbooks("NBA.xlsm").Activate
'Workbooks("NFL.xlsm").Activate
For Each wb In Application.Workbooks
If wb.Name Like "N??" Then ' "*Final*" Like *Final*
wb.Activate
Exit For
End If
Next wb

On Error GoTo Last

'Get the address from the RefEdit control
Address1 = RefEdit1.Value

'Set the SelectRange Range object to the range specified in the RefEdit control
Set SelectRange = Range(Address1)

'to convert cells selected
'action to convert to absolute

For Each c In SelectRange 'Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next c

'Unload the userform.
Unload Me

Last:

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
I could have several workbooks open. It's determined by the 'selection' of cells to convert (I assume). Selection comes from the UserForm.
I don't follow. How exactly is the range selected from the UserForm?
What does the UserForm look like, and where does it reference worobook/range?

Also note: Please use Code Tags when posting VBA code. It makes it much easier to read and work with.
See: How to Post Your VBA Code
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
332
Office Version
  1. 2019
Platform
  1. Windows
I don't follow. How exactly is the range selected from the UserForm?
What does the UserForm look like, and where does it reference worobook/range?

Also note: Please use Code Tags when posting VBA code. It makes it much easier to read and work with.
See: How to Post Your VBA Code
I just removed the activation, and it worked. I created a new workbook and tested it.
Maybe its something I don't have to worry about. I'm going to continue without the activation.
I will check your "how to post VBA code".
thanks for the reply
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,767
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top