Masterimp
09-08-2014, 06:30 AM
Hello Everyone,
I am trying to build a Macro that will allow a user to select an excel file and then select a sheet within the selected workbook. The macro will then pull data from the worksheet selected and paste it into a user form, so the user can check that the data that was pulled is correct. The user form will then populate this data into a row within the open workbook. I already have the latter part of the code in place and it is working. For the front half of this project I have build the following code
Private Sub Okay_Click()
Dim TgtName As String
Dim WB As Workbook
Application.ScreenUpdating = False
TgtName = Trim(Storyboard.Value) 'Storyboard.value is a userform textbox value'
If Len(TgtName) = 0 Then Exit Sub
Dim DirFile As String
DirFile = Storyboard.Value
If Len(Dir(DirFile)) = 0 Then
MsgBox "File does not exist"
Else
Set WB = Workbooks.Open(DirFile, True, True)
With ThisWorkbook.Worksheets("Definitions")
.Range("H1").Value = WB.Worksheets(Sheet.Value).Range("J3") 'Sheet.value is a userform textbox value'
.Range("H2").Value = WB.Worksheets(Sheet.Value).Range("D4")
.Range("H3").Value = WB.Worksheets(Sheet.Value).Range("D6")
.Range("H4").Value = WB.Worksheets(Sheet.Value).Range("D1")
.Range("H6").Value = WB.Worksheets(Sheet.Value).Range("D7")
.Range("H7").Value = WB.Worksheets(Sheet.Value).Range("J4")
.Range("H8").Value = WB.Worksheets(Sheet.Value).Range("I6")
.Range("H9").Value = WB.Worksheets(Sheet.Value).Range("O22")
.Range("H10").Value = WB.Worksheets(Sheet.Value).Range("S21")
End With
WB.Close False
Application.ScreenUpdating = True
End If
Unload Me
Reference.Hide
MsgBox "Please check the data that was pulled from the storyboard worksheet", vbInformation, "Data Check"
NewProject.Show
I have build a textbox with opens up a dialog box to select the workbook path for this code. This half of the code is working and as it currently sits paste's the pulled data into a hidden worksheet, so the userform will initialize with the data that was pulled. The problem is that I am making the user enter the sheet name on a user form before opening the workbook to pull the data. The code uses the sheet name within the text box to pull the data from the appropriate sheet. I would like to prompt the user with all the sheet names from the selected workbook and let them select the one to use, as all the sheets from the specified workbook will contain similar data and a certain one will have the most up to date data. This will make the code more user-friendly which is one of the objectives I am seeking with this project.
I understand that this is a mouthful, but if there is anyhelp any of you could provide, that would be greatly appreciated :)
I am trying to build a Macro that will allow a user to select an excel file and then select a sheet within the selected workbook. The macro will then pull data from the worksheet selected and paste it into a user form, so the user can check that the data that was pulled is correct. The user form will then populate this data into a row within the open workbook. I already have the latter part of the code in place and it is working. For the front half of this project I have build the following code
Private Sub Okay_Click()
Dim TgtName As String
Dim WB As Workbook
Application.ScreenUpdating = False
TgtName = Trim(Storyboard.Value) 'Storyboard.value is a userform textbox value'
If Len(TgtName) = 0 Then Exit Sub
Dim DirFile As String
DirFile = Storyboard.Value
If Len(Dir(DirFile)) = 0 Then
MsgBox "File does not exist"
Else
Set WB = Workbooks.Open(DirFile, True, True)
With ThisWorkbook.Worksheets("Definitions")
.Range("H1").Value = WB.Worksheets(Sheet.Value).Range("J3") 'Sheet.value is a userform textbox value'
.Range("H2").Value = WB.Worksheets(Sheet.Value).Range("D4")
.Range("H3").Value = WB.Worksheets(Sheet.Value).Range("D6")
.Range("H4").Value = WB.Worksheets(Sheet.Value).Range("D1")
.Range("H6").Value = WB.Worksheets(Sheet.Value).Range("D7")
.Range("H7").Value = WB.Worksheets(Sheet.Value).Range("J4")
.Range("H8").Value = WB.Worksheets(Sheet.Value).Range("I6")
.Range("H9").Value = WB.Worksheets(Sheet.Value).Range("O22")
.Range("H10").Value = WB.Worksheets(Sheet.Value).Range("S21")
End With
WB.Close False
Application.ScreenUpdating = True
End If
Unload Me
Reference.Hide
MsgBox "Please check the data that was pulled from the storyboard worksheet", vbInformation, "Data Check"
NewProject.Show
I have build a textbox with opens up a dialog box to select the workbook path for this code. This half of the code is working and as it currently sits paste's the pulled data into a hidden worksheet, so the userform will initialize with the data that was pulled. The problem is that I am making the user enter the sheet name on a user form before opening the workbook to pull the data. The code uses the sheet name within the text box to pull the data from the appropriate sheet. I would like to prompt the user with all the sheet names from the selected workbook and let them select the one to use, as all the sheets from the specified workbook will contain similar data and a certain one will have the most up to date data. This will make the code more user-friendly which is one of the objectives I am seeking with this project.
I understand that this is a mouthful, but if there is anyhelp any of you could provide, that would be greatly appreciated :)