Same userform for multiple sheet entries?

Posted by Garland on January 30, 2001 11:14 PM

Hi,

I have a 12 sheet cash balancing workbook, part of which is to enter the petty cash on hand. This consists of pennies, nickels, dimes, etc. I have a userform that I am trying to utilize to input this information into each sheet, for each succeeding day. The problem I am encountering is that the userform works correctly for the first sheet. However, when activated for the next sheet, the values from the preceeding sheet still appear in the userform. Changing any value changes the value on the previous sheet. Is there any way to initialize or force the userform to update the activesheet instead of the previous sheet?

Thanks for any help or suggestions...

Garland

Posted by Carl on January 31, 2001 6:38 AM

Do not fill in the ControlSource property manually. Instead use code to assign the ControlSource when the form is displayed.

E.g.
Private Sub UserForm_Initialize()
TextBox1.ControlSource = ActiveSheet.Name & "!A1"
End Sub

Posted by Garland on January 31, 2001 7:45 PM

Carl,

Thanks for responding. I have tried your suggestion and, when applied, I keep getting a "Run-time error 380 - Unable to set ControlSource property. Invalid property value".
Can you tell me where I might be getting off track?

Garland