Option button to select a worksheet

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Hello all!

This is driving me crazy! All I want to do is have my macro open an existing workbook and call up a Userform with 2 option buttons so the user can select which of the 2 sheets in that workbook the rest of the macro will apply to.
It seems like it should be so easy, but I've been working on it most of yesterday afternoon and most of today so far and I don't seem to be any closer than when I started! Here's the (totally failed) code I have so far:
VBA Code:
Private Sub OptionButton1_Click()

If Me.OptionButton1.Value = Then Sheets("AB").Select
ElseIf OptionButton2.Value = True Then Worksheets("CD").Select
End If

Unload Me
End Sub

(The 2 lines are different because I keep trying different things on the first one, so it's already had a lot of different wording from the 2nd line as each attempt fails)
Right now the 1st line - the OptionButton1.Value line - is failing at "Then" and saying "Expected Expression". And that happens without even trying to RUN the thing! When I type that, it immediately gives the error message.

Please somebody help! I have the ENTIRE rest of the macro ready to hand over to my coworker if this part will just work. (I wrote the rest of it first because, so save time, I just wrote it to work with 2 sheets in the same workbook. Now I'm trying to make it recognize the ACTUAL workbooks/worksheets it needs to work on.)

I should say that this code is very short because I thought I'd just get the option buttons doing what they need to do, then put that bit of code into the existing macro. This may or may not be a good approach; feel free to correct me if it's a stupid idea.

Thanks!
Jenny
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this and see if helps

VBA Code:
Private Sub OptionButton1_Click()
    Sheets("AB").Select
End Sub


Private Sub OptionButton2_Click()
    Sheets("CD").Select
End Sub

Dave
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
You should use the two option buttons then a command button. Then the command button click tests the option button value.
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Hi,
try this and see if helps

VBA Code:
Private Sub OptionButton1_Click()
    Sheets("AB").Select
End Sub


Private Sub OptionButton2_Click()
    Sheets("CD").Select
End Sub

Dave
That seems to work well, although the Userform didn't close after I clicked an Option Button. But I added Unload Me before each instance of End Sub and that fixed it.
It occurs to me that, since someone else is going to be using this macro, I'm going to have to make sure they also install the form creating the Userform in addition to the macro, right? Just in case she passes this part of her job on to someone else, I'm worried that she may not remember to tell them to import both things (macro and form). Is there any way at all to get this all to happen within the one macro? I doubt it, but if I don't ask and later find out it CAN be done, I'd feel stupid, LOL!

Thanks
Jenny
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You should use the two option buttons then a command button. Then the command button click tests the option button value.
Do you mean something like a Submit or Close button? If not, I'm not sure what you mean.

Thanks
Jenny
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
That seems to work well, although the Userform didn't close after I clicked an Option Button. But I added Unload Me before each instance of End Sub and that fixed it.

Jenny

Sorry, I only looked at the sheet selection issue you were having & omitted the Unload part of the code.

Not sure fully follow your thoughts on the workbook distribution - normally, you create your workbook with any required VBA & userforms(s) & when complete, others you pass it to should be able to use it.
Perhaps if you could share copy of your workbook with dummy data by placing it in a file sharing like dropbox, it would give forum better understanding of what you are trying to do.

Dave
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Dave,

I'm at work and we're not allowed to download anything (like dropbox) so I can't do that. So I'll have to upload it as an image. I'm sorry - I know that's harder for you to work with, but it's the only option I have.

In the process involved, the sheet called "Invoice" in the workbook (and the macro) is actually by itself in its own workbook. The two sheets called "AB" and "CD" are in a different workbook - only those 2 sheets in that workbook.

The person will be in the Invoice workbook and run the macro from there. The first thing it does is open the workbook with AB and CD in it and the person needs to select whether the data from the Invoice should be put on sheet AB or CD.

I have always just kept macros in my Personal Macro Workbook and that's how I've given the macros to whichever coworker will be using them. That's because I'm absolutely SURE that someone will forget to save a workbook as macro-enabled and something bad will happen. Or they'll, for whatever reason, copy the Invoice sheet, paste it into a blank workbook to start anew and then complain that the macro is missing when they try to use it. (Theoretically, the AB/CD workbook should always be located where it is, so the path to it SHOULD always be the same. With any luck at all.... )

Anyway, here's the full coding as it is for the single workbook attached to this. In the code below there is a line at the beginning that opens the ACTUAL workbook with AB and CD in it. Naturally, that'll mess up running it on the attached workbook, but if you take that line out, it does what I want it to do.)

VBA Code:
Sub FS_Claims_Invoice()
'JDrumm031221

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim dataWS As Worksheet
Set dataWS = wb.ActiveSheet
Dim temp As Workbook

Set temWB = Workbooks.Open("G:\Imports\Financial Services Information\CLAIMS\CLAIMS INVOICES\Claims Invoice Template (RTV).xlsx")

With Sheets("Invoice")
    CONSIGNEE = Application.Match("*CONSIGNEE:*", .Rows(4), 0)
    If Not IsError(CONSIGNEE) Then
        Sheets("AB").Range("B10:B15").Value = .Range(.Cells(5, CONSIGNEE), .Cells(10, CONSIGNEE)).Value
    End If
    DEPT = Application.Match("*DEPT*", .Rows(12), 0)
    If Not IsError(DEPT) Then
        Sheets("AB").Range("C16").Value = .Cells(13, DEPT).Value
    End If
   
    'Combine POs from invoice into template
    PO = Application.Match("*PO*", .Rows(12), 0)
    If Not IsError(PO) Then
        Dim C As Range, Rng As Range
        Set Rng = Range(.Cells(13, PO), .Cells(15, PO))
        For Each C In Rng
          If C > 0 Then
            Sheets("AB").Range("E16").Value = Sheets("AB").Range("E16") & ", " & C
          End If
        Next C
    End If
    'Get rid of leading comma in string
    Sheets("AB").Range("E16") = Right(Sheets("AB").Range("E16"), (Len(Sheets("AB").Range("E16")) - 2))
   
    RTV = Application.Match("*RTV#*", .Rows(7), 0)
    If Not IsError(RTV) Then
        Sheets("AB").Range("B19").Value = .Cells(7, RTV + 1).Value
    End If
    RA = Application.Match("*RA#*", .Rows(8), 0)
    If Not IsError(RA) Then
        Sheets("AB").Range("G16").Value = .Cells(8, RA + 1).Value
    End If
    'DR will identify how many data rows are on the invoice
    lr = Sheets("Invoice").Range("B" & Rows.count).End(xlUp).Row
    DR = lr - 17
   
    'DR2 will identify how many available rows are on the template
    Sheets("AB").Select
    LR2 = (Application.Match("*DISCOUNT*", Columns("F:F"), 0) - 2)
    DR2 = (LR2) - 18
   
    'NR tells the difference in rows between DR and DR2
    NR = DR - DR2
   
    'If there are more data rows than template rows this will insert enough rows on template to take the data.
    'If there are more than enough template rows for the data no rows will be inserted.
    If NR >= 1 Then
        Rows(LR2 & ":" & LR2 + NR).EntireRow.Insert , xlFormatFromLeftOrAbove
    End If
   
    LR3 = (Application.Match("*DISCOUNT*", Columns("F:F"), 0) - 2)
      
    Range("C19:J19").Copy Range("C20:J" & LR3)
   
    vendor = Application.Match("*VENDOR*", .Rows(16), 0)
    If Not IsError(vendor) Then
        Sheets("AB").Range("C19:C" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, vendor), .Cells(lr, vendor)).Value
    End If
   
    PIM = Application.Match("*PIM*", .Rows(17), 0)
    If Not IsError(PIM) Then
        Sheets("AB").Range("D19:D" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, PIM), .Cells(lr, PIM)).Value
    End If
   
    COMM = Application.Match("*COMMODITY*", .Rows(16), 0)
    If Not IsError(COMM) Then
        Sheets("AB").Range("E19:E" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, COMM), .Cells(lr, COMM)).Value
    End If
    Col = Application.Match("*COLOR*", .Rows(17), 0)
    If Not IsError(Col) Then
        Sheets("AB").Range("F19:F" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, Col), .Cells(lr, Col)).Value
    End If
    SZ = Application.Match("*SIZE*", .Rows(17), 0)
    If Not IsError(SZ) Then
        Sheets("AB").Range("G19:G" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, SZ), .Cells(lr, SZ)).Value
    End If
    QT = Application.Match("*Qty*", .Rows(17), 0)
    If Not IsError(QT) Then
        Sheets("AB").Range("H19:H" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, QT), .Cells(lr, QT)).Value
    End If
    TT = Application.Match("*Total*", .Rows(17), 0)
    If Not IsError(TT) Then
        Sheets("AB").Range("I19:I" & 19 + DR - 1).Value = Sheets("Invoice").Range(.Cells(18, TT), .Cells(lr, TT)).Value
    End If

End With

End Sub

The 1st image is the invoice and the 2nd is the desired result. So far, that part's going well.

The reason there's so much code spent finding the column headers is because some of the people creating the Invoice might or might not insert columns in various places, so I couldn't just use the existing column letters/numbers.
Invoice.JPG
Template-result.JPG


Jenny
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Uh oh; just had a bad thought

I was googling further and a post I saw led me to wonder something. After the user clicks one of the buttons to select the sheet, will the macro be able to know that that sheet is the one where the data should be pasted? I'm thinking not, but don't know how to TELL the code to use that selection!

Jenny
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
Uh oh; just had a bad thought

I was googling further and a post I saw led me to wonder something. After the user clicks one of the buttons to select the sheet, will the macro be able to know that that sheet is the one where the data should be pasted? I'm thinking not, but don't know how to TELL the code to use that selection!

Jenny

The optionbuttons select the required sheet so your code should just need to refer to the activesheet

I have not found time to work through the rest of your project & perhaps another here will be able to do this for you

Dave
 

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
The optionbuttons select the required sheet so your code should just need to refer to the activesheet

I have not found time to work through the rest of your project & perhaps another here will be able to do this for you

Dave
Awesome! That's a good thing!

No problem on the time. Since the rest of it works, I may just pass what I have on to the person that'll use it and they'll have to just do a bit at the beginning and then run the macro for the rest. I can add the coming improvements later.
Thanks so much for your help so far!
If I mark your first post at the start as "the solution" will I be able to mark a future addition to the code as a solution too? Or can you only mark 1 per topic? (Just want to be sure to give credit for a combination of answers that make up the TOTAL answer, LOL!)

Jenny
 

Watch MrExcel Video

Forum statistics

Threads
1,129,498
Messages
5,636,668
Members
416,935
Latest member
Atulcp

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
Top