PDA

View Full Version : Solved: Frame On Excel



kbsudhir
10-05-2008, 01:16 AM
Hi all,

I have a frame on my Excel Sheet name of the frame is CtlrSheet.

All the controls like Textbox, Labels & button are on that frame.
I want to write a code for the button click which is available on that frame.

Please provide some on this.

:doh: :doh:

Thanks
Sudhir

kbsudhir
10-05-2008, 01:25 AM
Well Well I was able to that as I made a fundamental mistake earlier.
I added a userform from VBA editor. And added teh necessary controls.


Now how to display the form. Which I want to do on a button click on the sheet.

Bob Phillips
10-05-2008, 02:40 AM
Just assign a macro to the button with the code



myForm.Show

kbsudhir
10-05-2008, 08:58 AM
Thanks XLD.

I have another query.

I have a list of variables which should be used across the code i.e in the code in form i.e. in the button click to capture the data in the textboxes in the form & use the same to do some manipulations in the cell value.

Which is done in another module.
I declared these variables in class module but I am not able use these variables in the code of my control form..?? Why. am I not able to declare the Global Variables.

I have declared them as
Public ModelNo() as string
its a dynamic array.
In a class module called "UniversalVariables"

Below is the code where I am using the below code
Private Sub DescArrnagement_Click()
ModelNo(0) = TextBox14.Value

MsgBox ModelNo
End Sub

I am getting the following error

"Sub or Function Not Defined"

Please guide.

Thanks
Sudhir

kbsudhir
10-05-2008, 09:17 AM
I want to use these variables throughout my project not just in that particular module.

I tried by declaring the same variable in the module of the form



Public ModelNo() As String


And commenting the earlier declaration.

Iam getting the follwoing error:

Compile error:
Constants, Fixed-Length strings, Arrays, user-defined datatypes & declare satements not allowed as the public members of the object

Please help as I have to use array.

Thanks
Sudhir

Bob Phillips
10-05-2008, 10:04 AM
You will need to create a project wide instance of your class, and then reference the variable through that class instance.

But it would be siumpler to create the variable as public (or global) in a standard code module rather than use a class.

kbsudhir
10-06-2008, 06:31 AM
Well I did that & now I am not getting the previous error.
But When I trying to use the array in the button click of teh form


ctr = 0
Prefix(ctr) = "Hello"


I am getting the follwoing error:
Subscript out of range.

I have declared this array as Public Prefix() as string

Please guide me to resolve this one.

Thanks
Sudhir

kbsudhir
10-06-2008, 06:34 AM
Should I use redim before entering data into the array..??
Then I have to use Preserve keyword also as I will enter only 1data at a time & I do not know how much data will be there.

kbsudhir
10-06-2008, 08:03 AM
Well I have figured this one out.

I should be using Redim Preserve

Thanks Everyone.
Sudhir