Pesky users, causing issues with userform

[gringo287]

Hi,


I need a little advise, on what can and cant be done with userforms. Mainly relating to issues around having one copy that all users have access to from a shared drive (that is currently running at 99% capacity), or if it is possible for all users to have their own copy with would eliminate the issue with space but more than likely cause more issues elsewhere.

ill bullet point my questions/query

The userform currently tracks/creates the users daily performance stats, based on their input and these stats can be emailed (with a really cool macro) to the manager at the end of the day.
These stats are wiped at the start of each new shift.
My aim is to create a weekly average that would be created automatically based on the users current stats plus the previous day/days stats.
Skipping over the obvious issue with the bursting to the brim shared drive.. is it possible to enable a useform to not have to depend on the file name, to open without errors.
My worry is that it is far too easy for a user to end up changing the file name and breaking the userform.
Is there such a thing as a macro that would prevent the users from being able to change the file name.

[BrianMH]

If the code is in the file that the user will be using then you can just refer to the workbook as thisworkbook. Then there is no need to reference it by name.

[gringo287]

Hi BrianMH,

Thank you for that. Please could you advise me how to impliment that change. Ive tried just adding thisworkbook, instead of the file name,but its expecting something else.

Private Sub cmdnew_Click()
Application.ScreenUpdating = False
Workbooks("filename").Activate
With ActiveWindow
.WindowState = xlNormal
.Height = 50
.Width = 50
End With
Sheets("lookups").Range("R2").Value = 1
Sheets("Calc").Range("A3").Value = Sheets("Calc").Range("A3").Value + 1
Sheets("Calc").Range("B3").Value = Sheets("Calc").Range("B3").Value + 1
cmdnew.Visible = False
cmdsame.Visible = False
chkconsult.Visible = True
lblproduct.Visible = True
cboproduct.Visible = True
CommandButton9.Visible = False
End Sub

[Paul_Hossler]

Probably this



ThisWorkbook.Activate




But by default the Activeworkbook is implied so maybe you don't even need


Workbooks("filename").Activate



BTW, 'Thisworkbook' refers to the WB containing the macro

'Activeworkbook' refers to currently selected WB

This can become important if there's an add-in containing the macro, or if one WB with the code is operating on another WB


Paul

[Bob Phillips]

You should be using a database and updating/querying that.

BTW you should never let anything get to 99% capacity, it is not good.

[gringo287]

You should be using a database and updating/querying that.

BTW you should never let anything get to 99% capacity, it is not good.

:rofl: No access to database and as im just an advisor that had the idea of creating this tracker and have no admin access etc. i should be fine with BrianMH' advice of ThisWorkbook.Activate as this will just use the individual advisors allocated drive space.