Unable to get the Text Property of the Range Class

[mar050703]

Hi Helpers

I have the following code

Private Sub CMBStk_Change()
    Sheets("Sheet1").Select
    Range("A35").Select
    ActiveCell.Value = CMBStk
    TxtFrm.Text = Sheets("Sheet1").Range("B35").Text
    TxtCost.Text = Sheets("Sheet1").Range("C35").Text
    
    Sheets("Stock").Select
    Range("A1").Select
Do
If ActiveCell.Value <> Sheets("Sheet1").Range("A35") Then
    ActiveCell.Offset(1, 0).Select
 End If
Loop Until ActiveCell.Value = Sheets("Sheet1").Range("A35")
    ActiveCell.Offset(0, 4) = "Yes"
    Sheets("Sheet1").Select
    Range("A2").Select
    
End Sub

Which in essence when the combobox "CMBStk" changes the code will run, which appears to be fine.

However when I click Ok on the user form (where "CMBStk" is), I get the error "Unable to get the Text property of the Range class".

A bit of code that I guess would potentially cause the problem from the Ok button is:

 If ChkStk.Value = True Then
Sheets("Stock").Select
Range("E1").Select
Do
    If ActiveCell.Value <> "Yes" Then
    ActiveCell.Offset(1, 0).Select
   End If
   
Loop Until ActiveCell.Value = "Yes"
   ActiveCell.Select
   Selection.EntireRow.Delete
Else
GoTo continue
End If
continue:
Unload Me

I simply require that the line be deleted if in column 4 the word "Yes" appears.

The good news is that it still does what I require it to do.

Hope that makes sense

mar050703

 

[zelgar]

I don't know if this will work, but try the following:

Change:
Range("A35").Select
ActiveCell.Value = CMBStk

To:
Range("A35").Select
Range("A35") = CMBStk

That way, Cell A35 is still selected and it's value is changed to CMBStk

 

[combo]

Are your controls (CMBStk, ChkStk, other) somehow linked with sheets via ControlSource or RowSource?

combo

 

[mar050703]

Combo

Not sure I fully understand, however... CMBStk is a dropdown in a userform that is linked to a source that comes from a sheet called "Stock" with in the same workbook. The list is effectively a dynamic range which changes with the number of rows within Column A which have data in them.

I have not yet had opportunity to see if zelgar's idea works.

Thanks

Mar050703

 

[mar050703]

Hi All

Sadly zelgar, your suggestion failed too, in exactly the same way. The debugger highlights in yellow the following code:

 Range("A35") = CMBStk

I am at a complete loss.

Thanks again

 

[combo]

I was thinking of a situation that when you change worksheet, changed cells fire userform's controls events. I would first check the sequence of controls' events (add Debug.Print ... at the beginning of each procedure) and change error trapping level (with "Break on All Errors" or "Break in Class Module" set the code will stop in the line where the error is).

combo

 

[SkipVought]

How to use the Watch Window as a Power Programming Tool faq707-4594

This technique will make it possible for you to observe what's going on with you objects and variables.

So my question is, what's the VALUE of CMBStk that you're assigning?

Range("A35") = CMBStk

Furthermore, here's how I'd do this, and NOTE THE REFERENCE TO THE USERFORM NAME...

Private Sub CMBStk_Change()
    With Sheets("Sheet1")
       .Activate
       .Range("A35").Value = CMBStk
       UserFormName.TxtFrm.Text = .Range("B35").Text
       UserFormName.TxtCost.Text = .Range("C35").Text
    
       .Range("A1").Select
       Do
          If ActiveCell.Value <> .Range("A35").Value Then
             ActiveCell.Offset(1, 0).Select
          End If
       Loop Until ActiveCell.Value = .Range("A35").Value
       ActiveCell.Offset(0, 4) = "Yes"
    
       .Range("A2").Select
    End With
    
End Sub

 

[mar050703]

Thank you SkipVought for your input.

I am trying to work through the debugging idea now.

I will try your code, with the one exception where you have

.Range("A1").Select

I will have to select a new sheet, as I require this next bit to be on a sheet called "Stock".

The value CMBstk has a rowsource of "Stock", which is a name within Name Manager - for which the code is:

=OFFSET(Stock!$A$2,0,0,COUNTA(Stock!$A:$A),1)[\code]

Thanks

 

[SkipVought]

Sorry I missed that.

Well you end the reference to the first sheet with End With and start a new sheet With reference, and .Activate that sheet.

 

[mar050703]

I have now got the debug on, and changed the code as per Skip (with the slight variation as mentioned) to:

Private Sub CMBStk_Change()
    Debug.Print
With Sheets("Sheet1")
    .Activate
    .Range("A35").Value = CMBStk
    UserFormName.TxtFrm.Text = .Range("B35").Text
    UserFormName.TxtCost.Text = .Range("C35").Text
End With
With Sheets("Stock")
    .Activate
    Range("A1").Select
Do
If ActiveCell.Value <> Sheets("Sheet1").Range("A35") Then
    ActiveCell.Offset(1, 0).Select
 End If

Loop Until ActiveCell.Value = Sheets("Sheet1").Range("A35")
    ActiveCell.Offset(0, 4) = "Yes"
    
End With
    Sheets("Sheet1").Select
    Range("A2").Select
    
End Sub

When I run the code with the debugger on, is stops at the line

 UserFormName.TxtFrm.Text = .Range("B35").Text

This line should be placing in the textbox "TxtFrm" the value of Sheet1, Cell B35

Just to explain then, Sheet 1 cell B35, and indeed C35 have a vlookup to the sheet "Stock", and pulling out the required data by referencing A35 (sheet1).

So the first bit of code places the value of CMBStk to Sheet 1 Cell A35.

It may be a bit round the houses, but it was the way I knew (or thought) that could get the data easily from the Stock Sheet to fill in the value for the 2 text boxes and the CMBStk.

I hope this make a bit more sense.

Thanks again

 

[SkipVought]

What is the name of your UserForm? THAT'S the name that needs to go in the 2 lines that reference controls on you userform.

 

[mar050703]

Skip

Yeah you got me, I missed that too - sorry.

So I have tested it, and all seems ok again, but it gives the original error on:

.range("A35").value = CMBStk

So the whole code works the textboxes on the userform populate ok, and all the other data from the userform does as it should (after clicking "CmdOk").

I think the problem is that the Private Sub is called CMBStk_Change, and obviously when the "CmdOk_Click" sub is run, it seems to change the value CMBStk (I guess back to the original state of being empty.)

Do I need to change the Sub of CMB_Change to something else like AfterUpdate?

I think we are getting there, thanks for your time and patience.

mar050703

 

[Andrzejek]

As a side note:
You do know that your combo’s Change event fires every time you type (or delete) a character in the combo, right? So if you type ‘Brown’, your Change event fires 5 times.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Andrzejek

Thank you for your note - I do now

I have tried Afterupdate, which does not appear to work. The closest I have come is Enter, but doe now work as I would like.

Is there a way round this, so that the Sub, only runs the once?

Not sure if that makes sense.

mar050703

 

[mar050703]

Still working on this.

I have tried changing the event to _click, but still I have the same issue.

It appears to run all the code (both the CMBStk_Click and the CMDOk_click events.

However towards the end of the CMDOk_click I have a piece if code that removes the chosed data from the "Stock" sheet (where the CMBStk is driven from), and therefore the CMBStk changes to the next one down in the list.

Is there a way to stop this happening please? I am at a loss (with my self taught knowledge of VBA.

mar050703

 

[Andrzejek]

If you allow user to type in your combo, I don't think there is much you can do about it ("so that the Sub, only runs the once"). But if you want your user to just choose an entry from your combo and not type anything, you can set combo's Style property to 2 - fmStyleDropDownList

Your Change event will not fire, but you can use Click event instead.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Just a piece of my mind:

I try to avoid using a UserForm in Excel.

Primarily , it isolates the user from the sheet. In my opinion, if you want to go that route, use Access where you absolutely want the user isolated from individual table access.

Then you must be very careful with your coding to trap errors and control the path through the controls, not that that's any different.

Rather, I'd urge you to consider direct access to the sheet, using controls directly on the sheet. One option to consider for controlled entry in a cell, is to use the Date > Validation--List feature. Naturally it would mean a totally different method of coding that would depend on Worksheet Events.

Just some thoughts.

 

[mar050703]

Andrzjek

Sorry for delayed response, I have been busy with other stuff

I have changed the style property to 2, and placed the code in the click event, but the same thing happens.

The code all works correctly, and as soon as the line is deleted, it appears I then get the error, I guess, as the CMBStk choice is no longer in the dynamic list.

Is there a way to cause the CMBStk to forget its selection, once the data has been entered into the sheet. If so how?

Thanks

 

[mar050703]

Andrejek

I have solved the issue.

Simply using the code

CMBStk.Value = Null

At the end of the CMBStk_Click() event sorts it.

Thanks for your help