Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-01-2019, 03:16 PM
ChettoDust ChettoDust is offline UserForm VLookUp Windows XP UserForm VLookUp Office 2010
Novice
UserForm VLookUp
 
Join Date: Mar 2019
Posts: 4
ChettoDust is on a distinguished road
Default UserForm VLookUp

Hello all,

I have created a userform to pull data from a worksheet to make changes then add the new data back into the worksheet. I am getting an 'object required error' on the second line
(If WorksheetFunction). My goal is to have tool makers be able to add there info into the form and hit save so that the new information is added back into the worksheet and also the information the tool maker added be saved to a second worksheet. My code is below:

The sheet I'm pulling data from is named "SOL"


The sheet I want all the new data to go back into "SOL" then only the data that the tool maker entered go to "Tool Room"

The only info the tool maker is adding is: Tool Maker, Tool Number, Hours, Date Completed
Code:
 
Private Sub Shop_TB2_AfterUpdate()
If WorksheetFunction.CountIf(SOL.Range("A8:A"), Me.Shop_TB2.Value) = 0 Then
MsgBox "Incorrect Order Number"
Me.Shop_TB2.Value = ""
Exit Sub
End If
With Me
.Date_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 2, 0)
.Name_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 3, 0)
.Area_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 4, 0)
.Account_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 5, 0)
.PartNum_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 6, 0)
.PartName_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 7, 0)
.Quantity_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 8, 0)
.RequestedDate_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 9, 0)
.Complete_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 10, 0)
.Build_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 11, 0)
End With
End Sub

Last edited by Pecoflyer; 03-02-2019 at 01:30 AM. Reason: Moved to correct sub forum
Reply With Quote
  #2  
Old 03-01-2019, 06:26 PM
NoSparks NoSparks is offline UserForm VLookUp Windows 7 64bit UserForm VLookUp Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

You will get better response(s) if you attach a sample workbook (not pictures of your sheets) with just enough data for us to work with.

Why the 'object required error' on the second line---> VBA considers SOL to be a variable not a worksheet.
Using Option Explicit the VBA would have indicated this before running the macro.
As-is, should be Sheets("SOL").

I suspect you'll get another error on that line because there is no bottom row for the CountIf range.

For loading those text boxes and writing back to the SOL sheet there are different ways,
my personal preference is using the Range.Find Method to locate Me.Shop_TB2.value, then using offset.

Writing to the "Tool Room" sheet...
I imagine Tool Maker is Me.Name_TB2, Tool Number is Me.PartNum_TB2, Date Completed is Me.Complete_TB2,
but don't know what Hours is, nor where any of these should be written on the sheet.
Reply With Quote
  #3  
Old 03-02-2019, 01:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline UserForm VLookUp Windows 7 64bit UserForm VLookUp Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi and welcome
please post in the correct subforum in the future.
I moved it for you this time
Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 03-04-2019, 06:28 AM
ChettoDust ChettoDust is offline UserForm VLookUp Windows XP UserForm VLookUp Office 2010
Novice
UserForm VLookUp
 
Join Date: Mar 2019
Posts: 4
ChettoDust is on a distinguished road
Default

The Tool Maker is ToolMaker_CB, tool number is ToolNumber_TB date completed is Complete_TB. My file is too big to send as an attachment. I have checked my sheet code and textbox name and both are correct.
Reply With Quote
  #5  
Old 03-04-2019, 09:03 AM
NoSparks NoSparks is offline UserForm VLookUp Windows 7 64bit UserForm VLookUp Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
My file is too big to send as an attachment.
I won't be mocking up a sample file to test anything with as I'd just be guessing at the data layout and user form you have.
You can decide whether or not that's necessary.

Something along the lines of this (untested) should load the user form,
and writing to the sheet can be done in a similar fashion with things inside the With-End With being swapped the other way round.
Code:
Private Sub Shop_TB2_AfterUpdate()
    Dim findString As String
    Dim fndRng As Range
    
findString = Me.Shop_TB2.Value
' change to correct range for find
Set fndRng = Sheets("SOL").Range("A:A").Find(What:=findString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not fndRng Is Nothing Then
    'MsgBox findString & " was found at  " & fndRng.Address
    With Me
        .Date_TB2 = fndRng.Offset(, 1)
        .Name_TB2 = fndRng.Offset(, 2)
        .Area_TB2 = fndRng.Offset(, 3)
        .Account_TB2 = fndRng.Offset(, 4)
        .PartNum_TB2 = fndRng.Offset(, 5)
        .PartName_TB2 = fndRng.Offset(, 6)
        .Quantity_TB2 = fndRng.Offset(, 7)
        .RequestedDate_TB2 = fndRng.Offset(, 8)
        .Complete_TB2 = fndRng.Offset(, 9)
        .Build_TB2 = fndRng.Offset(, 10)
    End With
Else
    MsgBox findString & "  Is An Incorrect Order Number"
    Me.Shop_TB2.Value = ""
    Exit Sub
End If
End Sub
Reply With Quote
  #6  
Old 03-04-2019, 01:57 PM
ChettoDust ChettoDust is offline UserForm VLookUp Windows XP UserForm VLookUp Office 2010
Novice
UserForm VLookUp
 
Join Date: Mar 2019
Posts: 4
ChettoDust is on a distinguished road
Default

That works great. What command would I assign to the save button for the userform to replace the data entered if it was changed.
Reply With Quote
  #7  
Old 03-04-2019, 02:12 PM
NoSparks NoSparks is offline UserForm VLookUp Windows 7 64bit UserForm VLookUp Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Haven't Fluff, bakerman or 6StringJazzer given you that yet ?
Would be a waste of time if you've already got it.
Reply With Quote
  #8  
Old 03-04-2019, 02:20 PM
ChettoDust ChettoDust is offline UserForm VLookUp Windows XP UserForm VLookUp Office 2010
Novice
UserForm VLookUp
 
Join Date: Mar 2019
Posts: 4
ChettoDust is on a distinguished road
Default

no, I have gotten it to add but it doesn't replace it.
Reply With Quote
  #9  
Old 03-04-2019, 08:02 PM
NoSparks NoSparks is offline UserForm VLookUp Windows 7 64bit UserForm VLookUp Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Most every Excel forum on the net has specific rules regarding cross posting.
I don't believe this forum actually has a written rule, but courtesy is always appreciated.
Have a read of this for an understanding of why it is important to let everyone know the situation:
https://www.excelguru.ca/content.php?184

As stated in post #5
Quote:
writing to the sheet can be done in a similar fashion with things inside the With-End With being swapped the other way round
Code:
If Not fndRng Is Nothing Then
    With Me
        fndRng.Offset(, 1) = .Date_TB2
        fndRng.Offset(, 2) = .Name_TB2
        fndRng.Offset(, 3) = .Area_TB2
        fndRng.Offset(, 4) = .Account_TB2
        fndRng.Offset(, 5) = .PartNum_TB2
        fndRng.Offset(, 6) = .PartName_TB2
        fndRng.Offset(, 7) = .Quantity_TB2
        fndRng.Offset(, 8) = .RequestedDate_TB2
        fndRng.Offset(, 9) = .Complete_TB2
        fndRng.Offset(, 10) = .Build_TB2
    End With
End If
As 'stuff' from a text box is text, some things, like dates, may need formatting for Excel to accept and display the way you want.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I return Vlookup only if Specific Criteria is met in 1 column of the Vlookup Array EcommDOC Excel 7 01-22-2018 11:00 AM
Help with UserForm walber Excel Programming 3 01-30-2017 12:12 AM
Userform calls other userform, then populate worksheet Lehoi Excel Programming 0 02-03-2016 02:58 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform Simoninparis Word VBA 2 09-21-2014 03:50 AM
UserForm VLookUp Is it possible to take an input from a UserForm in one document to a UserForm in a do BoringDavid Word VBA 5 05-09-2014 09:08 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft