offset from combobox on userform

[Jabba]

HI all

I have had the following code (basic I know but hey!) working from a private
sub on a form code (FrmEnterData) before.

I am trying to use the selection in the combobox CmbReason1 using select
case to populate the offset command to enter a "1" in the correct column.

It now only offsets (from the activecell) by one cell - it looks like the
variable 'reason' is returning 0 dfrom the select case.

I've tried moving the code to Module1, making it public, splitting the code
- all sorts but to no avail...

The annoying thing it worked on a different workbook. THAT workbook was
created at work with an older version of Excel - could that be it?

**Just noticed that when i type in 'FrmEnterData' VBA takes out the
capitalisations and leaves 'frmenterdata' - does this mean it doesn't
like/recognise the form?

But it DOES unload it !?!?!?!

Any help would be appreciated thanks

Jabba

***************************

Private Sub CmdContinue_Click()

Dim reason

Select Case frmenterdata.CmbReason1.Value

Case 0
reason = 0

Case 1
reason = 1

Case 2
reason = 2

Case 3
reason = 3

Case 4
reason = 4

Case 5
reason = 5

Case 6
reason = 6

Case 7
reason = 7

Case 8
reason = 8

Case 9
reason = 9

Case 10
reason = 10

Case 11
reason = 11


End Select

ActiveCell.Offset(0, 0).Value = TxtPolNo.Text
ActiveCell.Offset(0, reason + 1).Value = 1 'reason
ActiveCell.Offset(0, 13).Value = "Yes" 'options explained
ActiveCell.Offset(0, 14).Value = "No" 'quote requested
ActiveCell.Offset(0, 15).Value = "Yes" 'continue cancellation
ActiveCell.Offset(0, 16).Value = Date 'continue date
ActiveCell.Offset(0, 17).Value = TxtAddInfo.Text 'add info
ActiveCell.Offset(0, -1).Value = Date

Unload frmenterdata

End Sub

*************************

 

[sebastienm]

1.In that Sub , add
debug.print frmenterdata.CmbReason1.Value
What does it return in the Immediate Window?
2. Does the combobox have several columns?
3. Does the user see 'reason's like 0,1,2,3...in the combobox, or does he
see a corresponding text, eg: for reason=2, the combo shows "reason
blahblah"? If so you may have to look into the combobox TextColumn and
BoundColumn values to return

 

[sebastienm]

also, just thinking...
Could it be that the frmenterdata.CmbReason1.Value returns the string "1"
and therefore Case 1 never gets through 'cause it should Case "1".

Anyway, looking at your Select Case, it seems like you could just remove it
completel;y and instead, use:
reason= clng( val(frmenterdata.CmbReason1.Value) )

 

[Jabba]

Hi Sebastien

The combobox shows text
it has one column

tried that debug line and there is nothing in the immediates window

 

[Jabba]

Hi again Sebastien And thank you for your help

Tried your suggestion

reason= clng( val(frmenterdata.CmbReason1.Value) )

and now the sub does not put a 1 in any call at all

Tried retyping as FrmEnterData (the form name has capitalisations) but as
soon as I 'enter' at the end ofthat line it resets it to frmenterdata - does
this mean it's not finding the form properly??

 

[Jabba]

Hi Sebastien,

Just had a big play with it and

Select Case frmenterdata.CmbReason1.Value

is returning the text selected not the index

is there a single line of code to return the index of the selection made?

Many thanks again for your help

James = Jabba

 

[sebastienm]

The index of the selected item is:
frmenterdata.CmbReason1.ListIndex
- Starts at index 0 (zero; for first item in the combo)
- = -1 if no item is selected

So, you can use:
reason = frmenterdata.CmbReason1.ListIndex
instead of the Select Case statement

 

[Jabba]

Sebastien

Many many thanks - it works like a dream

I'll bang on a line make sure that a selection is made and then I'll be able
to sleep again

Regards

and thanks again