Form Controls Please Help

Joined
Sep 27, 2021
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2010
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!
 
Probably something within the macro code.

Post all of your code for review. The workbook with the code is even better.
 
The excel file was too big here are screenshots of the code and the worksheet. Thanks for your help.
 

Attachments

  • Estimate Worksheet Screenshot.jpg
    Estimate Worksheet Screenshot.jpg
    99.9 KB · Views: 5
  • VBA Code Screenshot.jpg
    VBA Code Screenshot.jpg
    91.7 KB · Views: 6
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.
 
There is a scaled down copy of the workbook. Thanks for your help.
 

Attachments

  • 20210921 - Viking Estimate - Copy.xlsm
    107.9 KB · Views: 6
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.

?????
 
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.
 
I'll play with the file for a bit and see if the same errors occur.
 
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
 
Back
Top