Help with interface?

Hey

Just completed my set of macros that i need to fully automate a process i am responsible for in work. However, i am a bit lost as to the best way to run them i.e. link them to buttons in excel etc. Is is possible to design some sort of interface in VBA and have it load with the spreadsheet

Normally i will be recieving a spreadsheet containing reams of data which have to be sorted and updated. This is what my macros will achieve. But what is the best/most efficient way of loading or applying those macros to a different set of data each month

Any help is much appreciated

Cheers

EDIT: I have since managed to design a form in visual basic and have all my sub routines linked to the various buttons and components on it. However, I am not sure how to have the form automatically display when the user loads up the excel sheet

Microsoft ISV Community Center Forums3

Hi Scot,

Try using event handlers, for example the Workbook_Open() event handler.

If you have made a UserForm at design time you can make it visible when you open the workbook as follows...

Private Sub Workbook_Open()

YourUserForm.Show

End Sub

Or you could call your macros directly in the Workbook_Open event handler.

The event handler needs to be placed in the code sheet associated with the workbook object.

I hope this helps.

Best regards

JFS

Thanks a million! Its all really starting to come together now. One last quick question - Im guessing this is very simple to do but i cant find trhe answer anywhere! - How do i add a minimise button to my form window At the minute when it loads i can't switch between it and my worksheet

Many thanks

Hi Scot,

Glad that helped.

Unfortunately the UserForm doesn't provide the functionality to allow you to minimize it. It is different to normal windows in that way.

But don't give up, you could try and make it non-modal, that means that it can be open and you can still access the worksheet under it.

The way to do that is to set ShowModal = False. This can be done at design time using the ShowModal property in the properties sheet for the UserForm, or at runtime with UserForm.ShowModal = False.

I hope this helps.

Best regards

Cheers! Thats solved the main problem.

What page would you suggest putting this code on

Private Sub Workbook_Open()

Form0.Show

End Sub

I have 13 sheets and 6 userforms and have tried putting it on all of them and when opening the worksheet, I cannot get the form to show

Thanks,

Nick Getz

I have a workbook that uses a userform and this is the code I use (this opens the userform as soon as I open the workbook).

Sub Auto_Open()

Dim mySheet As Worksheet

Set mySheet = Worksheets("Data")
mySheet.Select
Set mySheet = Worksheets("Hidden")
FirstTime = mySheet.Range("FirstTime")
If FirstTime Then 'TRUE
frmTankLevel.Show 'modal
End If
End Sub

The worksheet named "Hidden" is an extra sheet i have in my workbook that contains a cell called FirstTime and the text inside that cell is TRUE

I also have a command button on my spreadsheet ("Data"). Under that spreadsheet in the VBE I have the following code.

Private Sub cmdDataEntry_Click()
frmTankLevel.Show
End Sub

Hope this helps.

Tiger

Hi Nick,

Workbook_Open is an event that belongs to the Workbook object, it occurs every time that that workbook is opened.

So I would put the event handler in the code sheet associated with the workbook object (ie the ThisWorkbook object that you see in the project browser window).

Hope this helps.

JFS

Thank you for your assistance, I have it figured out and working now.

I have a workbook that uses a userform and this is the code I use (this opens the userform as soon as I open the workbook).

Sub Auto_Open()

Dim mySheet As Worksheet

Set mySheet = Worksheets("Data")
    mySheet.Select
Set mySheet = Worksheets("Hidden")
    FirstTime = mySheet.Range("FirstTime")
    If FirstTime Then 'TRUE
      frmTankLevel.Show 'modal
    End If
End Sub

The worksheet named "Hidden" is an extra sheet i have in my workbook that contains a cell called FirstTime and the text inside that cell is TRUE

I also have a command button on my spreadsheet ("Data"). Under that spreadsheet in the VBE I have the following code.

Private Sub cmdDataEntry_Click()
    frmTankLevel.Show
End Sub

Hope this helps.

Tiger

Thanks, thats a great peice of info! It suits me much better to have two buttons in my main worksheet allowing users to simply switch back and forth to the form. One question though - i mad the first command button and attched a sub routine to it to show the form, but now i've made a second one i can't find the thing in the VB Editior I want to be able to edit the properties in design mode like i did for the 1st button! I know its a basic Q but im totally lost here!

Thanks

EDIT - Its ok i managed to get it fixed now by playing about with it! :)