CODEWITHSUNDEEP
excelvbacombobox
Geographos
Geographos

Reputation: 1456

multiple macros in combobox

I have partially dealt with the calling 2 another value on value appeared in the combo box. However my job is repeatable (366 macros) (1 year). Is it possible to deal with it using some shorter and more convenient code?

Basically, I have: Column C -> already been allocated as a list index in the combo box Column D and column E are called both in combo box as the one macro (e.g for 1.01 is C1 and D1 as per as in the code below) 'JANUARY

Sub jan01()
Range("A5").Value = "=D1"
Range("B5").Value = "=E1"
End Sub
Sub jan02()
Range("A5").Value = "=D2"
Range("B5").Value = "=E2"
End Sub
Sub jan03()
Range("A5").Value = "=D3"
Range("B5").Value = "=E3"
End Sub
Sub jan04()
Range("A5").Value = "=D4"
Range("B5").Value = "=E4"
End Sub
Sub jan05()
Range("A5").Value = "=D5"
Range("B5").Value = "=E5"
End Sub
Sub jan06()
Range("A5").Value = "=D6"
Range("B5").Value = "=E6"
End Sub
Sub jan07()
Range("A5").Value = "=D7"
Range("B5").Value = "=E7"
End Sub
Sub jan08()
Range("A5").Value = "=D8"
Range("B5").Value = "=E8"
End Sub
Sub jan09()
Range("A5").Value = "=D9"
Range("B5").Value = "=E9"
End Sub
Sub jan10()
Range("A5").Value = "=D10"
Range("B5").Value = "=E10"
'...
End Sub

To run it with my combobox I used the "case" option:

Private Sub DateBox1_Change()
Select Case DateBox1
Case "1.01": jan01
Case "2.01": jan02
Case "3.01": jan03
Case "4.01": jan04
Case "5.01": jan05
Case "6.01": jan06
Case "7.01": jan07
Case "8.01": jan08
Case "9.01": jan09
Case "10.01": jan10
'...
End Select
End Sub

I used the combo box linked with the spin button as per as in the picture.strong text I will be grateful for any help.

Thank you in advance

Upvotes: 0

Views: 281

Answers (3)

Joseph
Joseph

Reputation: 5160

Something like this might work for you.

Option Explicit

Public Sub ProcessDay(ByRef ws As Worksheet, ByVal dayNumber As Long)
    ws.Cells(5, "A").Value = "=" & ws.Cells(dayNumber, "D").Address(0, 0)
    ws.Cells(5, "B").Value = "=" & ws.Cells(dayNumber, "E").Address(0, 0)
End Sub

And you can call it like so:

Private Sub DateBox1_Change()
    Dim dayNum As Long
    dayNum = dayNum = WorksheetFunction.Days(DateValue(Replace(DateBox1,".","/")), DateValue("1/1"))
    Call ProcessDay(Sheet1, dayNum)
End Sub

Basically the way it works is in the DateBox1_Change() code we take the text like 10.01 and convert it to a date like "10/01". Then we take that date string and convert it to a date and figure out how many days from Jan 1 it has been.

Note that this assumes that "10/01" means January 1st in your date time settings. For me, that means October 1st and I would need to manipulate the text another way to make it work for me. Please let me know if this works for you since it seams that my date time settings are not like yours.

Upvotes: 0

Profex
Profex

Reputation: 1390

If you know that the ComboBox is allocated/aligned with the list of dates in Column C, then all you should have to do is use the .ListIndex property to find out which item in the list is selected. Once you have that, that is your row value. Nice and simple...

Private Sub DateBox1_Change()
If DateBox1.ListIndex >=0 then ' If the selection in invalid it will be -1
    Range("A5").Value = "=D" & DateBox1.ListIndex + 2
    Range("B5").Value = "=E" & DateBox1.ListIndex + 2
End if
End Sub

Upvotes: 3

Nathan_Sav
Nathan_Sav

Reputation: 8531

Not sure what the values of D & E are, but you could do something like this

Range("A5").Value = range("d1").offset(datediff("d",dateserial(2018,1,1),dateserial(2018,split(DateBox1,".")(1)-1,split(DateBox1,".")(0))),0)

or

Range("A5").Value = "=" & range("d1").offset(datediff("d",dateserial(2018,1,1),dateserial(2018,split(DateBox1,".")(1)-1,split(DateBox1,".")(0))),0).address

Upvotes: 0

Related Questions