PDA

View Full Version : Issues about source in excel 2010.



thanhvanchi
12-12-2016, 01:04 AM
Hello everyone!


First and foremost - I'm rather new to VBA, and have only scratched the surface of the wonderful potential that VBA offers. Therefore, pardon me if my description might lack a bit


Currently (in MS Excel 2010), I am trying to create 3 x userforms where I have a GUI to select a specific excel file from a path directory, and then the whole path is pasted and shown in a textbox in each of the three userforms.


All of this is working flawlessly.


Now when I want to click my command button to open up all three different workbooks to copy a specific data range and paste it into my current sheet, it merely just freezes Excel (I get no error message).


Below is shown a code example of my VBA module to go through the operation - Here utilizing the path that I pasted in a textbox from one of the userforms (as a note here - I named each textbox from each userform as textbox1, textbox2 and textbox3 to avoid confusion).

Sub Update_CM_Eng()
Dim wbSource As Workbook
Dim wbDestination As Workbook

'open the source workbook from textbox value from userform and select the source sheet
Set wbSource = Workbooks.Open(TextBox1.Value)
'Set the destination workbook variable
Set wbDestination = Workbooks("Recruitment performance.xlsm")

'copy the source range
wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Copy

'paste the value at range
wbDestination.Sheets("Applicants_CM_Eng").Range("A2:N150").PasteSpecial (xlPasteValues)

Application.CutCopyMode = False

ActiveWorkbook.Save

Workbooks("recruit_data.xls").Close SaveChanges:=False

Sheets("Total Overview").Select

End Sub

Essentially when I click my command button, I call upon the three modules that I've created (code example as shown above).
My question is therefore: How do I set my source workbook (wbSource) as the workbook that I specified in the textbox value from a userform in the above code?

Thank's a lot!

Kenneth Hobs
12-12-2016, 06:21 AM
It is unclear what you need. Maybe posting the other two modules and the code that calls all three might help.

Update_CM_Eng would only show the current Userform's TextBox1.Value? That value must be the full drive:/path/filename.ext.

If your 3 modules are nearly the same, you can just pass the values needed so that it would be one module. This is one way to pass values. You can Dim a variable after Option Explicit (1st line in a Userform or Module). That makes it public so it would not need to be passed once value was assigned. You can store values into cells so that you can pass values to Private routines. You can store values in the registry for public use. You can store values in other files like TXT, INI, or such for public use.

If the workbook closed is the wbSource, you can use: wbSource.Close False

Here's an example for passing values to routines.
Userform:

Private Sub CommandButton1_Click()
tbVal TextBox1.Value
Unload Me
End Sub
Module:

Sub tbVal(aVal As String)
MsgBox aVal
End Sub

SamT
12-12-2016, 10:07 AM
Therefore, pardon me if my description might lack a bit
You are excused :)

On VBA and Excel Language
A UserForm is a Gui. Do you have three UserForm GUIs or Four UserForm GUIs?
Then there are Dialogs like FileDialog the User can use to select a File. That is also a crude GUI.

Tech note: In MS Windows, everything seen on the monitor is a GUI.

There is no "Whole Path". There is a Workbook FullName like "C:/MyFolder/Workbook1.xlsm" and a Workbook Path like "C:\MyFolder", (no trailing backslash). Workbook Name is like "Workbook1.xlsm".

Note. It is always best to use the "official" VBA or Excel Terminology.