Userform initialise help!

[justchris]

Ok, Well... this code i came up with does excatly what it's suppose to but... .. Not really. First off, It would be so much more effecient if i was able to call it from a module, but because i need the values to load when the userform is initialised it doesnt appear that i can. Is there some special trick to calling a sub statement from a userform initialise procedure ? Thats really where my problem lies.


I'll try to explain then I'll show a bit of my code. I'm creating a scheduling program and so far I like my set up. My sheet has names, days, and hours per day. A button on that sheet pulls up a userform which is linked to the names. Radio buttons are set up next to the names to select that person. I select a radio button and click next, and that pulls up another userform with the selected persons name, and hours in textboxes ( this is to make it easy to change there hours ) . I then have several if statements using cell refrence with a row and col variable if the select days start time is not available then it puts a "-" in that text box. if it is present then it writes the refrenced cell into the correct textbox.

Now, heres my problem, I have 15 employees... Thats a lot of if statements, Using my declared variables, (row, col ) I can use the same code over and over just by changing the row and col, Only problem is its like 1000 lines to do all employees and fill all the textboxes. So what I need is a way to fill a userform from either

a: a sub procedure
b: some other way

Please Login or Register  to view this content.

this is just a fraction of my code.

It goes all the way to friday. As you can see its a lot of stuff because I have to do it for each day of the week, then each day has 2 times, start time and a finish time. This gives me an error also, pretty much telling me theres to much code to initialise.

If anyone has any ideas as to how i can call this instead of having to write it over and over please tell me

Thanks in advance.
Chris

[Leith Ross]

Hello Chris,

The Form Initalize event is general used to load ComboBoxes, ListBoxes, make changes to User Form's properties, window appearence, etc. It looks to me like you are trying to place code into the Initialize event that should be in the Option Button Click event. Since each button is assigned to one employee, the text box should loaded when the option button is selected (clicked). You can then create one or more macros that can be called by the option button.

If have any more questions or need help in design I can help you. You can contact me via e-mail at LeithRoss@aol.com.

Hope this sheds some light on it,
Leith Ross

[justchris]

Awesome... that makes sense really. I just didnt know I could load values through a button. Thought I had to do it using the initialise. Just tested it and it seems like it will work.


Sorry for the long posted, Could have probably summed it up but I had a lot of caffein tonight


Thanks again Leith
Chris

[Leith Ross]

Hi Chris,

Better at times to risk being verbose than not say enough and be misunderstood.

Glad to help,
Leith Ross

[justchris]

eh... How can I define my col and row variables outside my module?

sub command_button_click() '<----EDIT
dim row as integer
dim col as integer
row = 16
col =10
call fill_forms_time(row, col)
end sub


sub fill_forms_time(row, col) '<---- EDIT
worksheets("sheet1").cells(col, row). value = 1
end sub

***edit***
Figured it out, added the (row,col) both my button and my module.


Just a simplified version of what im trying to do. There is a way right? lol


Thanks anyway

[Leith Ross]

Hi Chris,

Just an FYI since you got it to work.

You need to fully qualify the range object reference to gain access to the worksheet cells from a User Form. Since a macro is generally shared in the project, you need to build some referencing flexiblity into it. There are a couple of options. One would be to pass variables or another would be to use application references like ActiveCell, ActiveSheet, ActiveWorkbook, etc.

Fixed Worksheet Example (Hard Coded Reference):

'Select "A1" on "Sheet1"
Worksheets("Sheet1").Cells(1, 1).Select
Worksheets("Sheet1").Range("A1").Select

Using Any Worksheet (Active Sheet Reference):

ActiveSheet.Cells(1, 1).Select
ActiveSheet.Range("A1").Select

Hope this helps,
Leith Ross