Excel/VBA

Advertisement


Question
QUESTION: Tom

I got this code from you a while back.  it allows me to show pop form with data from specific cells.  It all works great however I need a few more cells added, I believe this one allows me to show 18 items, if I wanted to add 6 more, can u tell me how to change the coding.  I have tried before and only screwed it up - if you could tell me which line determines the total items that it will show that would help...thanks Tom

Private Sub UserForm_Initialize()   '<===  change
Dim sh As Worksheet, r As Range, r1 As Range
Dim k As Long, i As Long, cell As Range
Set sh = Worksheets("Customer")
Set r = sh.Range("Aw3:Aw8")
k = 1
For i = 0 To 3   '<=== change
Set r1 = r.Offset(0, i)
For Each cell In r1
appliances.Controls("Label" & k).Caption = cell.Text
k = k + 1
Next
Next
End Sub

ANSWER: Tony,

I altered the code to print out the label name and also to print out the variable "i" when it changes.  When "i" changes value, you are processing the next column.  So you start with column W and process rows 3 to 8 which is 6 cells.  Since i goes from 0 to 3, that is 4 columns of 6 rows each for a total of 24 cells/labels.

Private Sub UserForm_Initialize()   '<===  change
Dim sh As Worksheet, r As Range, r1 As Range
Dim k As Long, i As Long, cell As Range
Set sh = Worksheets("Customer")
Set r = sh.Range("Aw3:Aw8")
k = 1
For i = 0 To 3   '<=== change
Debug.Print "i=" & i
Set r1 = r.Offset(0, i)
For Each cell In r1
'appliances.Controls("Label" & k).Caption = cell.Text
Debug.Print "Label" & k
k = k + 1
Next
Next
End Sub

produced:

i=0   '<==  column W
Label1
Label2
Label3
Label4
Label5
Label6
i=1   '<==  column X
Label7
Label8
Label9
Label10
Label11
Label12
i=2   '<==  column Y
Label13
Label14
Label15
Label16
Label17
Label18
i=3   '<== column Z
Label19
Label20
Label21
Label22
Label23
Label24


so if you want to process another column of 6 cells/labels, you would loop

for i = 0 to 4  rather than for i = 0 to 3

if you wanted to process more cells per column, then you would change

Set r = sh.Range("Aw3:Aw8")

to

Set r = sh.Range("Aw3:Aw8")   to process 7 cells per column as an example.

Obviously you should make these changes to your original code - not my altered code that just prints out the labels.

--
Regards,
Tom Ogilvy






---------- FOLLOW-UP ----------

QUESTION: Tom

Thanks that work great!! I kinda got a light flash go off after reading your reply, truly thanks ..it helped!!

Also, I got this code from the site, which works fine as long as I only am using one cell, if I try and use more than one which (which are NOT next to each other, for example ...if I click on with the same worksheet cells G23, H25, K24, etc - I want to be able to have it execute that specific macro, I know doubleclick is an alternative but wanted to find a way so they don't have to doubleclick a cell to have it be executed....the code below is what I use presently and like I said works fine but only on that cell, have a tried a few different ways with adding other cells but nothing I do works....is this possible?




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Const myRange As String = "G23"

 If Not Intersect(ActiveCell, Range(myRange)) Is Nothing Then

   Application.ScreenUpdating = False
   
On Error Resume Next
   ActiveSheet.Protect Password:="noah2000"

   Sheets("view").Visible = True
   Sheets("forms").Select
   ActiveWindow.SelectedSheets.Visible = False
   Worksheets("forms").Visible = xlSheetVeryHidden
   
 Application.ScreenUpdating = True

   If Not Intersect(ActiveCell, Range(myRange)) Is Nothing Then

  End If
  End If

End Sub

ANSWER: Tony,

Based on what your code is doing, I am not sure why you would want to select multiple cells, but I believe the problem is using "ActiveCell".  The activecell is only one cell when multiple cells are selected.  The argument Target will pass in the a multicell reference if that is what is selected.  That said, when you are selecting multiple cells that are not contiguous, the selectionchange event would fire at each additional cell selected and that would be the activecell - so what you have should work, but I would change to Target from ActiveCell and see if that helps.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Const myRange As String = "G23"

 If Not Intersect(Target, Range(myRange)) Is Nothing Then

   Application.ScreenUpdating = False
   
On Error Resume Next
   ActiveSheet.Protect Password:="noah2000"

   Sheets("view").Visible = True
   Sheets("forms").Select
   ActiveWindow.SelectedSheets.Visible = False
   Worksheets("forms").Visible = xlSheetVeryHidden
   
 Application.ScreenUpdating = True

   If Not Intersect(Target, Range(myRange)) Is Nothing Then

  End If
  End If

End Sub


if you meant the trigger cells (currently G23)  

you can make that multiple cells with

Const myRange As String = "G23,H25,K24"

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: thanks but I didn't make myself clear - for each cell click it triggers and different event, such as g23 would take you to view, h25 would take you to Customer, K24 would take you do Records, and so on...

if I use this code wouldn't they all trigger the same event, go to view? and close forms?

I would like each cell to have a different event triggered

Tony

Answer
Tony,

> I want to be able to have it execute that specific macro,
certainly didn't convey to me what you are saying now.  

Here is an approach to execute different actions based on which cell is selected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if Target.count > 1 then exit sub

Select Case Target.Address(0,0)
 Case "G23"
   ' code to execute for cell G23 Selected


 Case "H25"
  ' code to execute for cell H25 selected


 Case "K24"
  ' code to execute for cell K24 selected


End Select

End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.