• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA Macro Buttons Not workign When Workbook Shared

Suraj John

New Member
Hey guys,
I created an Excel 2010 sheet that has a Start button that starts a timer and Stop and Reset button that stops and resets the timer respectively. But everytime I try to share the worksheet my button functionality for the Stop and Reset buttons is lost but for some reason my Start Button still remains active. I am posting the code below for you guys to have a look....Do kindly help out this confused noob:)
Code:
Dim maxrows As Long
Dim row, cols As Integer
Sub eventer()
  Call addtime
  Application.OnTime Now + TimeValue("00:00:01"), "Sheet1.eventer"
End Sub

Sub jukebox()
  MsgBox "Face the music"
End Sub

Sub addtime()
  maxrows = Sheet6.UsedRange.Rows.Count
  For i = 2 To maxrows
  row = Sheet6.Cells(i, 1).Value
  cols = Sheet6.Cells(i, 2).Value
  Sheet1.Cells(row, cols).Value = Sheet1.Cells(row, cols).Value + TimeValue("00:00:01")
  Next
End Sub

Sub list()
  Dim newss As Long
  maxrows = Sheet6.UsedRange.Rows.Count
  newss = maxrows + 1
  Sheet6.Cells(newss, 1).Value = row
  Sheet6.Cells(newss, 2).Value = cols
End Sub

Sub listdel()

  maxrows = Sheet6.UsedRange.Rows.Count
  For i = 1 To maxrows
  If Sheet6.Cells(i, 1).Value = row And Sheet6.Cells(i, 2).Value = cols Then
  Sheet6.Cells(i, 1).Delete shift:=xlUp
  Sheet6.Cells(i, 2).Delete shift:=xlUp
  Exit For
  End If
  Next

  
End Sub

Sub StartBtn_Click()
  Selection.Interior.ColorIndex = 6
  row = ActiveCell.row
  cols = ActiveCell.Column
  Selection.NumberFormat = "[h]:mm:ss;@"
  ActiveCell.FormulaR1C1 = "12:00:00 AM"
  Call list
End Sub

Sub Stop_Click()
  Selection.Interior.ColorIndex = 4
  row = ActiveCell.row
  cols = ActiveCell.Column
  Call listdel
End Sub
Sub Reset_Click()
  Selection.Interior.ColorIndex = -4142
  Selection.Value = "00:00:00"

  Call listdel
End Sub
 
There are quite a few limitation to shared workbooks.

With shared workbooks, even best designed VBA will eventually throw error, or generally stop working etc. I'd recommend to avoid using shared workbooks.

Instead, use individual workbook for people to use (stored in network drive) and use Macro or data link to collect info in the master sheet.
 
@Suraj John Could You send a sample file?
Shared workbooks are more than normal for me.



Hey @vletm ....sorry for the delay in my reply...i was trying to get my code done in googlescript...heres the sample file....It looks like my individual cell timers are taking too much time too process the code and hence slowing down my timers too ??
 

Attachments

  • PMS Schedule.xlsm
    274.7 KB · Views: 2
@Suraj John - the 1st challenge - You have used ActiveX-component(s)!
I cannot use those ... so what kind of component(s) are You using?
... maybe those 'Start/Stop'-buttons? anyway, where are those?
And - that file runs Macro at once ... with sample file ... not good feeling.
I'll continue checking later ...
 
@vletm...the way I designed the sheet is so that all the timer cells could run simultaneously(the yellow cells) till a particular cell is clicked and Stop button is pushed(the green cells) for only that cell to stop. The Start, Stop and Reset Buttons are the Active X controls buttons. You can view my code above or on my sheet if you prefer. It looks like the speed of the timers is accurate.. its just that on sharing the sheet my Stop and Reset buttons become inactive tho my Start button still works fine...The Start, Stop and Reset buttons are on the 1st and 2nd sheets itself...do let me kno if you can think of any fixes pls..
 
@Suraj John - testing ...
As I wrote, I cannot use ActiveX-components and that makes some challenges ..
So, I have to do 'Test' Buttons (both Sheet1&2) and
I made 'new Macros' for those too (Module1).
I added few lines to Your codes.
You can see better, how do this should work so far ...
So, test & report
 

Attachments

  • PMS_Schedule.xlsm
    324.4 KB · Views: 5
Hey @vletm ..Your sheet worked perfect and has helped me tweak my own sheet to more match yours..I am right now heavily testing my timer for bugs..Thanks a lot for your input on the sheet...Will let you know if I do get stuck on any of those bugs
 
Back
Top