Any ideas on how to improve this Userform

[oneblondebro]

Hi all,

Please see attached a Userform i have created with some help from online tutorials, I have 2 issue's which i cannot seem to fix and was wondering if you could help?

Issue 1, When i have multiple workbooks open and this workbook is not the Active one the userform wont work because it cannot find "Sheet 1", is there anyway to make the userform look for Sheet1 on this Workbook even if it's not the active workbook?

Issue 2, This one is really frustrating me, The coding allows the user to minimize the workbook but the CAPTURE TALLY userform stays visible and does not minimize, For some reason this does not work on my work computer (Accessed remotely), It minimizes the userform as well as the workbook, Any idea's how to get round this?

Thanks in advance for any help

[mdmackillop]

Try this

Option Private ModuleOption Explicit


Public UsefulinfoV81WB As Workbook


Sub OpenWorkbook()
Set UsefulinfoV81WB = ThisWorkbook
UserForm1.Show vbModeless
End Sub


Sub ClickBtn1()
Call Chk
With UsefulinfoV81WB
.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "CALL"
UserForm1.TextBox1.Value = Application.CountIf(.Sheets("Sheet1").Range("B:B"), "CALL")
UserForm1.TextBox3.Value = Round(Application.CountIf(.Sheets("Sheet1").Range("C:C"), "SALE") / Application.CountIf(.Sheets("Sheet1").Range("B:B"), "CALL") * 100, 2)
End With
End Sub


Sub ClickBtn2()
Call Chk
With UsefulinfoV81WB
.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "SALE"
'Delete next 2 rows if not working'
.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "CALL"
UserForm1.TextBox1.Value = Application.CountIf(.Sheets("Sheet1").Range("B:B"), "CALL")
UserForm1.TextBox2.Value = Application.CountIf(.Sheets("Sheet1").Range("C:C"), "SALE")
UserForm1.TextBox3.Value = Round(Application.CountIf(.Sheets("Sheet1").Range("C:C"), "SALE") / Application.CountIf(.Sheets("Sheet1").Range("B:B"), "CALL") * 100, 2)
End With
End Sub


Sub Chk()
If UsefulinfoV81WB Is Nothing Then Set UsefulinfoV81WB = Workbooks("CAPCOUNT v2.0.xlsm")
End Sub