Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing Data from Userform to Module 2

Status
Not open for further replies.
NCSUVBAnewb

NCSUVBAnewb

Technical User
Jun 24, 2011
14
US
Hey all,

I've got a VBA module that runs a userform that prompts for a certain set of data, finds and opens a file based on the user input, inputs and formats that data, and saves to a new directory. I'm having difficulties passing the data as variables from the UserForm I created back to the module to be inserted into the file path that needs to be opened. Code below:

Module:
Code: 
Option Explicit

Dim MonthDay As String
Dim InsString As String
Dim CalDate As String

Sub InputDate()
    FindInput.Show
    Call ImportData
End Sub

Sub ImportData()
    Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\0801\" & InsString & "080104.TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth
    
    'Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\" & MonthDay & "\" & InsString _
    '& "_" & MonthDay & CalDate & ".TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth
    
    Call FormatImported
End Sub

Sub FormatImported()
...the remaining code is just formatting stuff which is irrelevant.
NOTE: The commented out stuff above is how I would like the file path to ultimately look, but it isn't importing the variables as needed.

UserForm:
Code: 
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Public Sub cmdOK_Click()
    YYMM.Value = MonthDay
    cboCoord.Value = InsString
    cboDay.Value = CalDate
    
    Exit Sub
End Sub

Private Sub UserForm_Initialize()
    YYMM.Value = ""
    With cboCoord
        .AddItem "16-41"
        .AddItem "24-09"
        .AddItem "24-41"
        .AddItem "32-17"
        .AddItem "32-25"
        .AddItem "40-25"
    End With
    cboCoord.Value = ""
    With cboDay
        .AddItem "01"
        .AddItem "02"
        .AddItem "03"
        .AddItem "04"
        .AddItem "05"
        .AddItem "06"
        .AddItem "07"
        .AddItem "08"
        .AddItem "09"
        .AddItem "10"
        .AddItem "11"
        .AddItem "12"
        .AddItem "13"
        .AddItem "14"
        .AddItem "15"
        .AddItem "16"
        .AddItem "17"
        .AddItem "18"
        .AddItem "19"
        .AddItem "20"
        .AddItem "21"
        .AddItem "22"
        .AddItem "23"
        .AddItem "24"
        .AddItem "25"
        .AddItem "26"
        .AddItem "27"
        .AddItem "28"
        .AddItem "29"
        .AddItem "30"
        .AddItem "31"
    End With
    cboDay.Value = ""
    
    YYMM.SetFocus
End Sub


Basically I think the problem lies somewhere in the variable declaration (as public/private/etc) or the storing of the variable in the userform.

Any ideas?
 
Sort by date Sort by votes


where is InsString assigned a value?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
You have not stated what you mean by "having difficulties passing the data as variables from the UserForm I created back to the module". I'm guessing you mean that you're not sure how to handle the control of the userform as a means to acquire data from the user and then pass this data back to the calling sub in the module.

If so, here is a very simple example which does just that. It does not implement your requirement, in terms of file creation etc, but it does show how to pass the data, so you should be able to figure out how to use this process to meet your needs.

Open a new workbook. In the code area, insert a userform. On the userform, add a textbox and a commandbutton.
Insert a module.

Add the following code to the userform:
Code: 
Dim MyData As String
Private Sub CommandButton1_Click()
MyData = TextBox1.Text
Hide
End Sub

Public Property Get GetData() As Variant
GetData = MyData
End Property

Add the following code to the module:
Code: 
Sub PassingDataExample()
Dim datatoshow As String
UserForm1.Show
datatoshow = UserForm1.GetData
Cells(1, 1) = datatoshow
End Sub

Now in Excel run the macro "PassingDataExample". On the userform, enter some text in the textbox then hit the commandbutton.

This will take the text and enter it, via a variable in the module, into a cell on the workbook.

If you look at what the above does, and how, I think you will be able to solve your problem.

Does that help?

Tony
 
Upvote 0 Downvote
Skip,

InsString is supposed to be filled by the UserForm value for the ComboBox.



Tony,

Thank you very much for writing that out for me, I'll implement that now and see how things go.
 
Upvote 0 Downvote
Tony,

Here are the adjustments I made:

Module:
Code: 
Option Explicit

Dim MonthDay As String
Dim InsString As String
Dim CalDate As String

Sub InputDate()
    FindInput.Show
    InsString = FindInput.GetData
    Call ImportData
End Sub

Sub ImportData()
    Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\0801\" & InsString & "080104.TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth
    
    'Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\" & MonthDay & "\" & InsString _
    '& "_" & MonthDay & CalDate & ".TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth
    
    Call FormatImported
End Sub

Sub FormatImported()

Userform:
Code: 
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
    MonthDay = YYMM.Text
    InsString = cboCoord.Text
    CalDate = cboDay.Text
    Hide
End Sub

Public Property Get GetData() As Variant
GetData = InsString
End Property

Private Sub UserForm_Initialize()
    YYMM.Value = ""
    With cboCoord
        .AddItem "16-41"
        .AddItem "24-09"
        .AddItem "24-41"
        .AddItem "32-17"
        .AddItem "32-25"
        .AddItem "40-25"
    End With
    cboCoord.Value = ""
    With cboDay
        .AddItem "01"
        .AddItem "02"
        .AddItem "03"
        .AddItem "04"
        .AddItem "05"
        .AddItem "06"
        .AddItem "07"
        .AddItem "08"
        .AddItem "09"
        .AddItem "10"
        .AddItem "11"
        .AddItem "12"
        .AddItem "13"
        .AddItem "14"
        .AddItem "15"
        .AddItem "16"
        .AddItem "17"
        .AddItem "18"
        .AddItem "19"
        .AddItem "20"
        .AddItem "21"
        .AddItem "22"
        .AddItem "23"
        .AddItem "24"
        .AddItem "25"
        .AddItem "26"
        .AddItem "27"
        .AddItem "28"
        .AddItem "29"
        .AddItem "30"
        .AddItem "31"
    End With
    cboDay.Value = ""
    
    YYMM.SetFocus
End Sub

I'm still getting the error that the filename specified in the ImportData subroutine does not exist. It's missing the

Code: 
 Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\0801\" [b]& InsString &[/b] "080104.TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth

Variable so it's just searching for 080104.TXT. I've tried adding
Code: 
Sub InputDate()
    FindInput.Show
    InsString = FindInput.GetData
    Excel.Application.Workbooks.Add
    ActiveSheet.Range("A1").Value = InsString

    'Call ImportData
End Sub

But nothing appears in the cell. I might just be frustrated and have the syntax of that command done wrong, but I just can't figure out why the variable isn't being filled.
 
Upvote 0 Downvote


Again, WHERE do you assign InsString?????

Please post this code and the declaration and location of the declaration.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I assign InsString here:

Code: 
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
    MonthDay = YYMM.Text
    [u][i]InsString = cboCoord.Text[/i][/u]
    CalDate = cboDay.Text
    Hide
End Sub

Public Property Get GetData() As Variant
GetData = InsString
End Property

Private Sub UserForm_Initialize()
    YYMM.Value = ""
    With cboCoord
        .AddItem "16-41"
        .AddItem "24-09"
        .AddItem "24-41"
        .AddItem "32-17"
        .AddItem "32-25"
        .AddItem "40-25"
    End With
    cboCoord.Value = ""
    With cboDay
        .AddItem "01"
        .AddItem "02"
        .AddItem "03"
...

So whatever the value is from the ComboBox:Coord should input into InsString
 
Upvote 0 Downvote
The declaration is the same as in all previous windows of code
 
Upvote 0 Downvote


So you put in a BREAK anc have checked the value of
Code: 
"D:\3DMGT\08ALLTXTS\0801\" & InsString & "080104.TXT"
I'd do a
Code: 
Debug.Print "D:\3DMGT\08ALLTXTS\0801\" & InsString & "080104.TXT"
and that file/path is valid?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
The problem here is that you seem to be hoping that InsString is a global variable that both your module and your userform can access.

Unfortunately, you have actually got it declared as a Private variable - which means that it is available to all functions and procedures in the module in which it is declared, but not anywhere else. Simply change

Dim InsString As String

to

Public InsString As String
 
Upvote 0 Downvote
Firstly, you are not using the GetData() property you have defined, you are still calling InsString. This will give you the value of that variable as defined in your module, not in the userform.

Secondly, you need to to reference the userform.

So, ImportData() should read:
Code: 
Sub ImportData()   
Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\0801\" & FindInput.GetData & "080104.TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth    
Call FormatImported
End Sub

Thirdly, you need to ensure that InsString is defined at form level in the userform, so include the line:

Private InsString as string

before any subs in Findinput. Otherwise, CmndOK() and Getdata() will be referring to their own versions of the variable and not "talking to each other".

Is that clear?

If you define Monthday InsString and CalDate as public at top level in FindInput, then you don't need to pass them via properties, but that is generally regarded as bad practice. Using the property method I've outlined is generally a better bet.

You might find it useful to look up "Scope of variables" in a programmers guide or the help, or see:

Try also googling "Public variable vs. Property" for more info on why you should retrieve InsString via a property rather than using a public variable.

Tony
 
Upvote 0 Downvote
N1GHTEYES said:
Firstly, you are not using the GetData() property you have defined, you are still calling InsString. This will give you the value of that variable as defined in your module, not in the userform.

Secondly, you need to to reference the userform.

So, ImportData() should read:

Code: 
Sub ImportData()   
Workbooks.OpenText Filename:="D:\3DMGT\08ALLTXTS\0801\" & FindInput.GetData & "080104.TXT", Origin:=437, StartRow:=1, DataType:=xlFixedWidth    
Call FormatImported
End Sub
Click to expand...

Not entirely correct - the OP is calling ImportData from InputDate, where InsString is set from FindInput.GetData prior to calling ImportData. Of course, because of how InsString is declared, GetData does not return what the OP is expecting, but that is a slightly different matter - an issue covered by the remainder of your observations (although I'd argue that well-placed global variable is often a perfectly acceptable and viable technique)

 
Upvote 0 Downvote
strongm - you are, of course correct. I missed that reference when I skimmed through what he was doing, though the code I suggested would still have worked.

As to global vars Vs properties, well, I wouldn't deign to argue with the guy who produces code I can only marvel at, and from whom I've probably learned more interesting and useful stuff than from any other single source. I certainly use globals myself on enough occasions, but I was just giving the "received wisdom" to somebody who appears to regard himself as a newbie - at least according to his handle.

Tony
 
Upvote 0 Downvote
strongm and N1GHTEYES, you guys have been a HUGE help!

I avoided using the Public variable declaration as I understand that can be dangerous and instead am using the Public Properties. N1GHTEYES, I made the changes you recommended and I now have that GetData property working for all variables needed.

strongm your commentary helped me analyze the project, and now everything's going smoothly!

Now that I've got this working for 1 file, next step is to batch files together and see if I can do it thataway.

Thank you SOOOO much!!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
588
huiettcm
huiettcm
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
836
remeng
remeng
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
759
strongm
strongm
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
645
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top