Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

**pike** · Apr 10th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

check the data as it may have incorrect entry
maybe set the name ranges up each time

Code

Private Sub myTab_Change()
    
    Dim arrData, c As Long, i As Long
     Dim itmLvl As Range, itm As Range
    arrData = Worksheets("CALC").ListObjects("tbl_stats").DataBodyRange
    i = myTab.SelectedItem.Index + 1
    For c = LBound(arrData) To UBound(arrData)
        If c = i Then
 Call cbxItemLvl_Change
 Call cbxItem_change
            cbxItemLvl.Value = arrData(c, 2)
            cbxItem.Value = arrData(c, 1)
            cbxAttrb1.Value = arrData(c, 3)
            tbxAttrb1.Value = arrData(c, 4)
            cbxAttrb2.Value = arrData(c, 5)
            tbxAttrb2.Value = arrData(c, 6)
            cbxAttrb3.Value = arrData(c, 7)
            tbxAttrb3.Value = arrData(c, 8)
            cbxAttrb4.Value = arrData(c, 9)
            tbxAttrb4.Value = arrData(c, 10)
            lblBns.Caption = arrData(c, 11)
            lblBnsVal.Caption = arrData(c, 12)
        End If
    Next c
End Sub

Display More

but its getting messy .. what are we trying to do?

ivanian · Apr 10th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

I tried to add that but now I'm getting an error with Match property. Anyway, yeah sorry I didn't explain the flow. This should be flow.

- When form opens, it'll initialize ComboBoxes then check if there are existing data from 'tbl_stats'
- Checks what tab is active, if first tab is active, it'll load first row. If second tab is active it'll load the 2nd row.
- Data changes whenever the user update it. Update button will send the values of the controls to the 'tbl_stats'
- Again, if the first tab is active, the data will be sent to first row. If second tab is active it'll send the date to 2nd row and so on.

**pike** · Apr 10th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

I don't real follow the logic with the different named ranges but try..

Code

' <~~~~~~~~~~~~ Check for existing data ~~~~~~~~~~~~>
Private Sub myTab_Change()


    Dim arrData, c As Long, i As Long
    Dim itmLvl As Range, itm As Range
    Dim attrb As Range, xTab


    arrData = Worksheets("CALC").ListObjects("tbl_stats").DataBodyRange
    i = myTab.SelectedItem.Index + 1
    For c = LBound(arrData) To UBound(arrData)
        If c = i Then




            Set attrb = ThisWorkbook.Names("attrb").RefersToRange


            xTab = myTab.SelectedItem.Index    ' Determine selected tab
            If xTab = 0 Then
                Set itmLvl = ThisWorkbook.Names("wpLvl").RefersToRange
                Set itm = ThisWorkbook.Names("wp").RefersToRange
            Else
                Set itm = ThisWorkbook.Names("suit").RefersToRange
                Set itmLvl = ThisWorkbook.Names("suitLvl").RefersToRange
            End If
            Call cbxItemLvl_Change
            Call cbxItem_change


            cbxItemLvl.Value = arrData(c, 2)
            cbxItem.Value = arrData(c, 1)
            cbxAttrb1.Value = arrData(c, 3)
            tbxAttrb1.Value = arrData(c, 4)
            cbxAttrb2.Value = arrData(c, 5)
            tbxAttrb2.Value = arrData(c, 6)
            cbxAttrb3.Value = arrData(c, 7)
            tbxAttrb3.Value = arrData(c, 8)
            cbxAttrb4.Value = arrData(c, 9)
            tbxAttrb4.Value = arrData(c, 10)
            lblBns.Caption = arrData(c, 11)
            lblBnsVal.Caption = arrData(c, 12)
        End If
    Next c
End Sub

Display More

ivanian · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

Quote from pike;768941

I don't real follow the logic with the different named ranges but try..

Actually, the reason behind the different named ranges is that if you'll take a look at the DB sheet — 'wp' and 'suit' has different number of columns and column headers. Bad design of table I guess? but they really do have different 'Attributes' (as I'd like to call it) that's why I had to separate them. Nevertheless, I will try that code as soon as I get off work. Thanks for all the help you've given so far. I'll get back to you the soonest.

**pike** · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

You've done well .. it good to use named ranges and named formulas in your code .. I would have done it different but with the code you have scripted you can use the my_tab change() to first clear all the comboboxes, making sure there is a blank value in the combobox list, then populate the comboboxes via the tab index to implement the different named ranges before repopulating with the recordset data.

ivanian · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

Quote from pike;768944

You've done well .. it good to use named ranges and named formulas in your code .. I would have done it different but with the code you have scripted you can use the my_tab change() to first clear all the comboboxes, making sure there is a blank value in the combobox list, then populate the comboboxes via the tab index to implement the different named ranges before repopulating with the recordset data.

Should I remove the populating of comboboxes from the useform initialize and just put it all in tab change event? Then just call tab_change at userform initialize? In that way, the procedures do not get redundant? If you were to do it? How would you approach it? I'm open to suggestions as I'm still learning and to be exposed to different techniques is a great benefit.

**pike** · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

Hi,
Yes that would be good
divide each process up into functions or subs that you can call and combine as required
look for routines that are repeated and spread them and if possible make them functions

ivanian · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

May I ask how would you approach the coding if you were to do the script that I'm making? I won't be able to touch my PC today as I have some matters to attend to. It would be good to get a different view from someone who has the experience and skills. I'm trying to visualize the flow whenever I'm not actually coding and try to implement it whatever ideas I came into me. It would be nice if I could get your view as well. Of course, only if you don't mind.

Thanks.

**pike** · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

All good and your doing fine. You have already used a function for the unique list ect..
you can add more dimension's for the life of the userform example

Code

Option Explicit
Dim colTbxs As Collection    'Collection Of Custom Textboxes
Dim itmLvl As Range, itm As Range

and pass or use them in other function or subs. but you've already done all the good stuff

ivanian · Apr 11th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

Quote from pike;768956
All good and your doing fine. You have already used a function for the unique list ect..
you can add more dimension's for the life of the userform example

Code

Option Explicit Dim colTbxs As Collection 'Collection Of Custom Textboxes Dim itmLvl As Range, itm As Range

and pass or use them in other function or subs. but you've already done all the good stuff

I see. It's relieving to know that I'm actually on the right track. I have tried the code you wrote at #23 and it seems to do the trick. Hopefully, that solves it all.

Thanks for all the help. I've taken a lot of your time (sorry about that)

**pike** · Apr 12th 2016

Re: Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

no need to be sorry .. thanks for posting ..

Could Not Set The Value Property. Invalid Property Value. Tabstrip Change Event

Participate now!

Share

Users Viewing This Thread