Hi, i have an userform which, after it opens, allow me to input some info to certain cells in a table. And i use the below macro. However, i need to modify it to help me add more info to the table. For this, i need two things:
1. I need the macro to address to specific rows and columns, because now the macro is using ex: column C,D,E,F,G ( it's using 5 consecutive columns). And i need to be able to use for example, column C, then E, then G, then I, then K....non continuous colums and rows. Now, if i change the letters, the macro still shows 5 continuous columns in dropdown.
if i change this:
Case "Raft 1"
strColL = "C"
strColR = "D"
strColR = "E"
strColR = "F"
strColR = "G"
into this:
Case "Raft 1"
strColL = "C"
strColR = "E"
strColR = "G"
strColR = "I"
strColR = "K"
,then, i need the userform dropdown to display only this non continuous columns, and to put my datas to selected columns..
The same with rows:
this:
lnRowTop = 7
lnRowBot = 8
lnRowBot = 9
lnRowBot = 10
lnRowBot = 11
into this:
lnRowTop = 7
lnRowBot = 9
lnRowBot = 11
lnRowBot = 13
lnRowBot = 15
I need the macro to use in the dropdown the columns and rows of my choosing.
2. The second thing i need to modify, is to add another textbox on the same userform (i can do this), and this text box to input datas to the first cell below the selected dropdown target.
If the selected target is "Shelf1 / Column1 / Level5", then the new textbox to put datas to the cell below this target.
Shelf1 / Column1 / Level1 = C7
The new textbox to put the datas to C8
Can this be done?
Thanks,
Apollo
1. I need the macro to address to specific rows and columns, because now the macro is using ex: column C,D,E,F,G ( it's using 5 consecutive columns). And i need to be able to use for example, column C, then E, then G, then I, then K....non continuous colums and rows. Now, if i change the letters, the macro still shows 5 continuous columns in dropdown.
if i change this:
Case "Raft 1"
strColL = "C"
strColR = "D"
strColR = "E"
strColR = "F"
strColR = "G"
into this:
Case "Raft 1"
strColL = "C"
strColR = "E"
strColR = "G"
strColR = "I"
strColR = "K"
,then, i need the userform dropdown to display only this non continuous columns, and to put my datas to selected columns..
The same with rows:
this:
lnRowTop = 7
lnRowBot = 8
lnRowBot = 9
lnRowBot = 10
lnRowBot = 11
into this:
lnRowTop = 7
lnRowBot = 9
lnRowBot = 11
lnRowBot = 13
lnRowBot = 15
I need the macro to use in the dropdown the columns and rows of my choosing.
2. The second thing i need to modify, is to add another textbox on the same userform (i can do this), and this text box to input datas to the first cell below the selected dropdown target.
If the selected target is "Shelf1 / Column1 / Level5", then the new textbox to put datas to the cell below this target.
Shelf1 / Column1 / Level1 = C7
The new textbox to put the datas to C8
Code:
Option Explicit
'
' Userform has the following controls:
' - ComboBox called "cbShelf"
' - ComboBox called "cbCols"
' - ComboBox called "cbLevels"
' - TextBox called "TextBox1"
' - CommandButton called "cbOK" (OPTIONAL)
' When the textbox is changed the contents of the text box is written to the
' cell who's column is indicated in the Cols combobox and who's row is indicated
' in the Levels combobox, relative to the range specified in.
'
Private lnRowTop As Long, lnRowBot As Long, lnColL As Long, lnColR As Long
Private Sub UserForm_Initialize()
Me.cbShelf.AddItem "Raft 1"
Me.cbShelf.AddItem "Raft 2"
Me.cbShelf.ListIndex = 0
Me.cbShelf.Style = fmStyleDropDownList
Me.cbCols.Style = fmStyleDropDownList
Me.cbLevels.Style = fmStyleDropDownList
End Sub
Private Sub cbShelf_Change()
Dim i As Long
Dim strColL As String, strColR As String
Select Case Me.cbShelf.Value
Case "Raft 1"
strColL = "C"
strColR = "D"
strColR = "E"
strColR = "F"
strColR = "G"
lnRowTop = 7
lnRowBot = 8
lnRowBot = 9
lnRowBot = 10
lnRowBot = 11
Case "Raft 2"
strColL = "C"
strColR = "D"
strColR = "E"
strColR = "F"
strColR = "G"
lnRowTop = 15
lnRowBot = 16
lnRowBot = 17
lnRowBot = 18
lnRowBot = 19
End Select
lnColL = Range(strColL & ":" & strColL).Column
lnColR = Range(strColR & ":" & strColR).Column
'COLUMNS:
'Remove all entries from the cb:
For i = Me.cbCols.ListCount - 1 To 0 Step -1
Me.cbCols.RemoveItem i
Next i
'Add new entries to the cb:
For i = 1 To lnColR - lnColL + 1
Me.cbCols.AddItem "Coloana " & i
Next i
Me.cbCols.ListIndex = 0
'ROWS:
'Remove all entries from the cb:
For i = Me.cbLevels.ListCount - 1 To 0 Step -1
Me.cbLevels.RemoveItem i
Next i
'Add new entries to the cb:
For i = 1 To lnRowBot - lnRowTop + 1
Me.cbLevels.AddItem "Nivel" & (lnRowBot - lnRowTop + 1) - i + 1
Next i
Me.cbLevels.ListIndex = 0
Call UpdateTextbox
End Sub
Can this be done?
Thanks,
Apollo