How to get this userform to work on another sheet

K

kandso

New Member
Joined
Jun 29, 2019
Messages
7
Hi,

I use this userform that works great on the sheet named PRODUCTs that we type our product names directly into column F.

Code: 
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Option Explicit
Private Sub UserForm_Initialize()
    
    Dim rngSource As Range
    Dim rngMyCell As Range
    Dim wsMySheet As Worksheet
    
    Set wsMySheet = Sheets("PRODUCTs")
    
    wsMySheet.Range("$F$5:$F$" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
    Set rngSource = wsMySheet.Range("F5:F" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    
    ComboBox1.Clear
    
    For Each rngMyCell In rngSource
        ComboBox1.AddItem rngMyCell.Value
    Next rngMyCell
    
    wsMySheet.AutoFilterMode = False 'Remove above filter

End Sub
Private Sub ComboBox1_Change()

    Call SetQTY(False)

End Sub
Private Sub CommandButton1_Click()

    Call SetQTY(True)
    
End Sub
Sub SetQTY(blnOutputToSheet As Boolean)

    Dim rngFound As Range
    Dim wsMySheet As Worksheet
    Dim lngLastRow As Long
    
    Set wsMySheet = Sheets("PRODUCTs")
    lngLastRow = wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row
    Set rngFound = wsMySheet.Range("F5:F" & lngLastRow).Find(What:=CStr(ComboBox1.List(ComboBox1.ListIndex)), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
    If ComboBox1.ListIndex <> -1 And Not rngFound Is Nothing Then
        If blnOutputToSheet = True Then
            rngFound.Offset(0, 1).Value = CDbl(TextBox1.Value)
        Else
            TextBox1.Value = CDbl(rngFound.Offset(0, 1))
        End If
    End If

End Sub</code>


I'm trying to use it on another sheet named ADJUSTMENTS and can't get it to input the values entered in the userform onto that sheet. I've tracked down what I believe to be the issue... on the sheet ADJUSTMENTS the product names in column F are not entered directly. They are pulled from the sheet named PRODUCTs using

Code: 
=IF(PRODUCTs!F5=0,"    ",PRODUCTs!F5)
=IF(PRODUCTs!F6=0,"    ",PRODUCTs!F6)
=IF(PRODUCTs!F7=0,"    ",PRODUCTs!F7)
etc...


I've changed the sheet name in the code to ADJUSTMENTS, and the userform launches fine and the products in Column F are populated in the combobox, but when I submit a value nothing happens. I'm lost on what else needs to be changed to get the userform to work on the sheet ADJUSTMENTS.


Code: 
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Option Explicit
Private Sub UserForm_Initialize()
    
    Dim rngSource As Range
    Dim rngMyCell As Range
    Dim wsMySheet As Worksheet
    
    Set wsMySheet = Sheets("ADJUSTMENTS")
    
    wsMySheet.Range("$F$5:$F$" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
    Set rngSource = wsMySheet.Range("F5:F" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    
    ComboBox1.Clear
    
    For Each rngMyCell In rngSource
        ComboBox1.AddItem rngMyCell.Value
    Next rngMyCell
    
    wsMySheet.AutoFilterMode = False 'Remove above filter

End Sub
Private Sub ComboBox1_Change()

    Call SetQTY(False)

End Sub
Private Sub CommandButton1_Click()

    Call SetQTY(True)
    
End Sub
Sub SetQTY(blnOutputToSheet As Boolean)

    Dim rngFound As Range
    Dim wsMySheet As Worksheet
    Dim lngLastRow As Long
    
    Set wsMySheet = Sheets("ADJUSTMENTS")
    lngLastRow = wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row
    Set rngFound = wsMySheet.Range("F5:F" & lngLastRow).Find(What:=CStr(ComboBox1.List(ComboBox1.ListIndex)), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
    If ComboBox1.ListIndex <> -1 And Not rngFound Is Nothing Then
        If blnOutputToSheet = True Then
            rngFound.Offset(0, 1).Value = CDbl(TextBox1.Value)
        Else
            TextBox1.Value = CDbl(rngFound.Offset(0, 1))
        End If
    End If

End Sub</code>

Any guidance would be greatly appreciated.

Thank you!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
.
I have not compared both macros to determine is they are the same, except for the sheet name.

If the first macro works with the Sheet Name PRODUCTS ... and you haven't changed ANYTHING in the code, then the macro will work with the other Sheet Name ADJUSTMENTS.

So the issue is most likely a spelling error / typographical error / etc.
 
Upvote 0
Hi Logit,

Thanks for looking, but it's not a spelling or typographical error. As in my first post the macro works fine on the sheet where products are typed into column F that populates the combobox (it will also work on any sheet as long as whatever is in column F is typed in)..

The exact same macro (with only changing the sheet name) will not work on any sheet that is pulling the data in Column F from another sheet.

That is where my problem is... I don't know if there is something in the macro code that can be changed up a bit to make it work on a sheet with column F populated from another sheet.
 
Upvote 0
.
I believe you'll need to post your complete workbook to a cloud website like DropBox.com or similar.
 
Upvote 0
I find it unusual to have IF formulas putting 4 spaces into the cells, but that may be what's wanted.

If you F8 one line at a time through UserForm_Initialize and look at the "ADJUSTMENTS" sheet right after the filter is applied, is it what you expect ?
 
Upvote 0
Maybe it can be done a different way... Is it possible to change the code below and have the "Sub SetQTY" output the textbox data into a different sheet name? I tried changing the sheet name in the Sub SetQTY section but that didn't work.

So the initialize code stays the same populating the combobox from sheet PRODUCTs, then the macro outputs the data entered into textbox1 into a sheet ADJUSTMENTS (using the same offset).

Code: 
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Option Explicit
Private Sub UserForm_Initialize()
    
    Dim rngSource As Range
    Dim rngMyCell As Range
    Dim wsMySheet As Worksheet
    
    Set wsMySheet = Sheets("PRODUCTs")
    
    wsMySheet.Range("$F$5:$F$" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
    Set rngSource = wsMySheet.Range("F5:F" & wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    
    ComboBox1.Clear
    
    For Each rngMyCell In rngSource
        ComboBox1.AddItem rngMyCell.Value
    Next rngMyCell
    
    wsMySheet.AutoFilterMode = False 'Remove above filter

End Sub
Private Sub ComboBox1_Change()

    Call SetQTY(False)

End Sub
Private Sub CommandButton1_Click()

    Call SetQTY(True)
    
End Sub
Sub SetQTY(blnOutputToSheet As Boolean)

    Dim rngFound As Range
    Dim wsMySheet As Worksheet
    Dim lngLastRow As Long
    
    Set wsMySheet = Sheets("OUTPUT DATA TO A DIFFERENT SHEET")
    lngLastRow = wsMySheet.Cells(Rows.Count, "F").End(xlUp).Row
    Set rngFound = wsMySheet.Range("F5:F" & lngLastRow).Find(What:=CStr(ComboBox1.List(ComboBox1.ListIndex)), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
    If ComboBox1.ListIndex <> -1 And Not rngFound Is Nothing Then
        If blnOutputToSheet = True Then
            rngFound.Offset(0, 1).Value = CDbl(TextBox1.Value)
        Else
            TextBox1.Value = CDbl(rngFound.Offset(0, 1))
        End If
    End If

End Sub</code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
</code>
 
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...e-this-macro-output-to-a-different-sheet.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Sorry about that, missed that rule.


Also posted here for possible help...


[URL="https://www.excelforum.com/excel-programming-vba-macros/1281288-possible-to-make-this-macro-output-to-a-different-sheet.html#post5147072[/URL]
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Sum the total working hours when the Qty Completed become value except 0 automatically
Replies
0
Views
329
yoon
Y
T
  • Question
How to copy Row from one sheet to another?
Replies
2
Views
447
Tanquen
T
A
  • Question
how implement code on userform for multiple ranges names instead of sheets
Replies
0
Views
268
Ali M
A
K
  • Question
How to input decimal places with this userform
Replies
2
Views
665
kandso
K
I
  • Question
Edit current working Userform advice
Replies
3
Views
485
ipbr21054
I

Forum statistics

Threads
1,215,440
Messages
6,124,882
Members
449,193
Latest member
PurplePlop

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top