Userform macro

ApolloID

Well-known Member
Joined
Jun 8, 2010
Messages
769
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

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,214,641
Messages
6,120,694
Members
448,979
Latest member
DET4492

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top