Change cell value based on current open sheet

naira · Jan 7th 2015

Hi!

I was wondering if it is possible to change the value of a cell based on the currently active worksheet of another workbook?

I have 2 workbooks, Book1.xlsm (macro enabled workbook) and Book2.xlsx (normal workbook), opened simultaneously.

If the currently active worksheet in Book2.xlsx is Sheet1, the value of Cell A1 in Sheet1 of Book1.xlsm should be 1.
If the currently active worksheet in Book2.xlsx is Sheet2, the value of Cell A1 in Sheet1 of Book1.xlsm should be 2.

Finally, I want to run a macro based on whenever this cell value changes. One could run it based on Current Active Sheet Cell value change, but the problem is that the cell where the value is changing is not on the currently active sheet.

Thanks,

Naira.

cytop · Jan 7th 2015

Re: Change cell value based on current open sheet

Easily...

Add the following to the ThisWorkBook class module

Code

Option Explicit
Option Compare Text


Private WithEvents xlWB As Excel.Workbook
Public Property Set MonitorWB(wb As Excel.Workbook)


   Set xlWB = wb
   
End Property
Private Sub xlWB_SheetActivate(ByVal Sh As Object)


   ThisWorkbook.Sheets("Sheet1").Range("A1").Value = Sh.Index
   
End Sub




Private Sub xlWB_BeforeClose(Cancel As Boolean)


   Set xlWB = Nothing
   
End Sub

Display More

You then need something to choose a workbook to monitor from those loaded...

Perhaps a userform with a combobox (named 'cbo'), Style set to DropDownList, and 2 buttons (cmdOK & cmdCancel). cmdOK is not enabled by default.

Following code in userform

Code

Option Explicit
Option Compare Text


Private Sub cbo_Change()


   cmdOK.Enabled = cbo.ListIndex > 0
   
End Sub


Private Sub cmdCancel_Click()


   Unload Me
   
End Sub


Private Sub cmdOK_Click()


   Set ThisWorkbook.MonitorWB = Application.Workbooks(cbo.Text)
   Unload Me
   
End Sub




Private Sub UserForm_Initialize()


   Dim w As Excel.Workbook
   
   With cbo
      For Each w In Application.Workbooks
      
         If w.Name <> ThisWorkbook.Name Then
            .AddItem w.Name
         End If
      Next
   
      If .ListCount > 0 Then
         .AddItem "Choose workbook...", 0
      Else
         .AddItem "No available workbooks."
         .Enabled = False
      End If
      
      .ListIndex = 0
      
   End With
   
End Sub

Display More

Does not include the 'Finally...' - that was added while I was posting.

Change cell value based on current open sheet

Participate now!

Share