Results 1 to 9 of 9

Thread: Form Controls Please Help

  1. #1

    Form Controls Please Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a spreadsheet which I use to produce estimates primarily composed of lines items with corresponding quantities, lengths, etc. In order to avoid having to type in the numbers for each I inserted a form control spin button for the quantity, feet and inches so 3 spin buttons per row and approximately 25 rows. Initially this setup worked fine however after I saved and exited the worksheet then opened it later these spin buttons would be linked to a cell other than the one I linked to it. After a lot of time spent trying to figure out what was going on I realized that there were multiple copies of spin buttons usually 3 or 4 on top of the one spin button I inserted which was linked to the correct cell. I tried getting rid of the spin buttons and using a scroll bar but the same thing occurred scroll bars were being created on top of my original scroll bar. I cannot find any help with this specific problem on the web. Please someone help me!

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    321
    Articles
    0
    Excel Version
    2007
    Probably something within the macro code.

    Post all of your code for review. The workbook with the code is even better.

  3. #3
    The excel file was too big here are screenshots of the code and the worksheet. Thanks for your help.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	VBA Code Screenshot.jpg 
Views:	5 
Size:	91.7 KB 
ID:	10651   Click image for larger version. 

Name:	Estimate Worksheet Screenshot.jpg 
Views:	4 
Size:	99.9 KB 
ID:	10650  

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    321
    Articles
    0
    Excel Version
    2007
    When posting a workbook, it needs to have just a sample of the working data. Like 10 rows of data ... 10 records.

    You'll need to make a COPY of the workbook ... reduce it's size, then post the edited workbook.

    Picture are nice but impossible to work with. Keep in mind that no one wants to recreate what you already have sitting in front of you. Too much time and effort to do that
    while volunteers are ... well ... volunteering.

  5. #5
    There is a scaled down copy of the workbook. Thanks for your help.
    Attached Files Attached Files

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    321
    Articles
    0
    Excel Version
    2007
    I am unable to duplicate the issue here. The "add scrollbar" macro creates the shapes as indicated. If you run that macro multiple times, duplicates of the scroll bars will be created in the same locations.

    ?????

  7. #7
    Yes I am aware that running the macro multiple times will create multiple scroll bars in the same location. But thats not the issue I am having trouble with. I use this workbook to produce estimates as I have already stated when i need to produce an estimate I open this file input the client's name and then save the workbook using the date and the clients name so any changes made to the workbook are not saved to the Viking Metal Estimate file they are saved to a new file named using the date and clients name. However there are times when I open the workbook and click the scroll bar and the value changes in a cell a few rows up NOT the cell I linked the scroll bar too then I have to delete the usually 3 or 4 scroll bars that are on top of my original scroll bar before I get back to the original scroll bar which is linked to the correct cell.

  8. #8
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    321
    Articles
    0
    Excel Version
    2007
    I'll play with the file for a bit and see if the same errors occur.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,031
    Articles
    0
    Excel Version
    365
    Try this (you should only ever need to run it once, and never again):
    Code:
    Sub add_scrollbar()
    With ActiveSheet
      .ScrollBars.Delete 'removes all scrollbars from the sheet
      For i = 40 To 54
        Set cb = .Shapes.AddFormControl(xlScrollBar, .Range("F" & i).Left, .Range("F" & i).Top, .Range("F" & i).Width, .Range("F" & i).Height * 0.75)    'l,t,w,h
        cb.ControlFormat.LinkedCell = "G" & i
      Next
    End With
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •