RefEdit Image (Form) Controls in Excel VBA 2007

[pvharibabu]

I have actually two questions:

1. I have an userform with few RefEdit (Form) controls, an Image (Form) control and a Command Button (Form) control. I have set different images when I click different RefEdits using Image1.Picture = LoadPicture("D:Documents and SettingshbabuDesktopLoadCombPicture.JPG"). As you see, if I open the this xlsm file in a different computer, it won't work as the picture files do not exist. How do I make this work regardless the computer in which this xlsm is opened?

2. How do I check whether or not the range selection is valid? I am trying to set such a way that the form exits (with Command Button click) only when the range selection in all the RefEdits are valid. If any of the range selection is invalid, msgbox pops up referring to that particular RefEdit and the cursor goes to the first (checked) RefEdit with invalid range.

Please let me know if you want my spreadsheet, I can send it to your email id.

Thanks in advance - Hari

 

[pvharibabu]

I worked out for Question #1. Created more Image controls with corresponding pictures embedded in them and making others not visible except the associated one when I click the that RefEdit

 

[SirJB7]

Hi, pvharibabu!

About your 2nd question, would you please clarify what are the definitions for a range to be determinated as valid? You can consider uploading a sample file as indicated in second green sticky post at this forums main page.

Regards!

 

[pvharibabu]

Hi SirJB7, this is the link of the file, https://dl.dropbox.com/u/97890138/LoadComb%20Macro%2014%20August%202012-Upload.xlsm

Please ignore my code. If the value of the range is either nil or invalid string is considered as invalid. For example, a valid string is

Sheet1!$C$6:$O$24 and invalid string is Sheet1!$$6:$$24

or null string. I have another questions too.

3. How do I change the caption of the RefEdit when I click on little minus (-) button? For example, in Goal Seek, if I click on red little arrow, I see the caption for the Set Cell RefEdit as Goal Seek - Set Cell:

 

[pvharibabu]

Another question:

4. When I check the validity of the range and gets the cursor back to that RefEdit using

SetFocus

, I want to see the associated picture in the Image. How do I do that?

 

[SirJB7]

Hi, pvharibabu!


I was looking at your uploaded workbook, opened the user form, and wondered what should I enter as valid ranges for testing purposes, since I didn't understand what were each drop down combo meaning.


So here I am, asking if you could please give me a few examples of actual data regarding your uploaded file, so as to help me understand how it works.


Furthermore it'd be very useful if you could tell me which would be the scope or range of your real data, how many rows, how many columns, only one sheet or more... Perhaps we can automatize the 4 range selection within the user form


Regards!

 

[Guest]

and each time toward a strange place .arvixe. welcome you to Taiwan and Taiwan,sac lancel,php/User:60932701732#timeless_attitude_s http://shoestringventure. I will succeed.
Abercrombie,jordan,air jordans,jordans shoes,http://blog.fabricproperty.com/index.php/2012/02/hot-table-mishkins/, if I continue to try,air jordans shoes,php? survival of the fittest,sac longchamp pas cher,http://chandoo.org/forums/topic/extract-all-the-numeric-values-from-the-right-of-any-alphanumeric-string/page/2?replies=39#post-37208,Other related education resource network and there are almost No.Until the work of women are cleared,cheap air jordans,title=User:14039907293#back_when_to_give_y http://wikicom/what-are-the-exact-uploading-and-conversion-settings-of-clip-bucket/ Henceforth will I recognize that each day I am tested by life in like manner.Lu Kunyang is also difficult to suppress the four years of feelings.
Lan Ling Wang -- purple. will let all the people eat or drink. I will succeed.AB.only to the original commitment to youde/mediawiki/index. corrupt officials.michael kors handbags,ralph lauren pas cher,http://blog.lefigaro.fr/medias/2011/03/sxsw-11-blake-mycoskie-fondate.html#comments,org/index.php?ralph lauren.
com/recreation/burningman/index.php/User:34864758274#her_tears_fall_asle Henceforth will I recognize that each day I am tested by life in like manner. 82. php/User:Jpfcdykwqjr#It. Related articles: http://blog.

 

[pvharibabu]

Hi SirJB7, going in detail about what I am actually after.


This VBA is used to create one or two additional worksheets in which a syntax or code is produced, which can be copied to a software editor for a structural analysis purpose (I am a Civil Engineer).


I have checked this code and it does perfectly what I wanted. Of course, as you see, I have removed the VBA code which does this task, because I want to program it for error handling.


If the user choose to go to the second, third or fourth RefEdit either without selecting a range or by selecting an invalid range (if you see my previous post about definition of invalid string,

Sheet1!$$6:$$24

- represents an user deletes the column letters between the $ symbols by mistake - is an invalid string) for the first RefEdit, I want to set the cursor back to the first one for a valid range selection again with a popup error message. By this, I would like to make sure a valid range is selected for a particular RefEdit before going to the next RefEdit.


Coming back to the real data of these ranges

- Load Factors Matrix is C6:O24. User can select any range between C6 to O24. Infact, the real range or scope could be anything but in a matrix form having few rows and columns

- Load Combinations Column is B. Always one column

- Load Case Titles Row is 4. Always one row

- Load Numbers Row is 5. Always one row

- All this data would be one worksheet

If all my input is correct and click on "Continue" button, it does produce the syntax or code as I said before.


Hope this helps for your better understanding.


For your information: The syntax or code I was taking about would be something like below. There are actually two sets of syntax.

Set 1: 101 = +1DL+1LL+1WdOT+1Temp

Set 2: 101 1 1.0

101 2 1.0

101 5 1.0

101 13 1.0

These two sets are for the combination 101 (in Row 6)

As I mentioned before, I have deleted the programming for this to avoid confusion.


Actually, I have done this before without userform. But it requires lot of input (MsgBox are used) like first (6) & last (24) row and first (C) & last (O) column for load factors matrix and selecting one software out of two and selecting one analysis type out of two. So, In order to make it simpler, I am trying to have an userform with RefEdits for all data ranges and Check Box and Radio Buttons for software and analysis selection.


Please let me know if you want more information, I can send you my earlier version of this file or can chat on yahoo or skype or gtalk.


Thanks for your time and help SirJB7! Hope I haven't confused you with my long list of info.


Regards

Hari

 

[SirJB7]

Hi, pvharibabu!


Let me see if I got it.


a) the factor matrix is a submatrix or rectangular contiguous (or non contiguous?) range(s) within C6:O24 (let's use as example E10:G14)

b) the combination columns should be B6:B24 (whole column), B10:B14 (related to factor matrix) or any other else?

c) the case titles should be C4:O4 (whole row), E4:G4 (related to factor matrix) or any other else?

d) the number rows should be C5:O5 (whole row), E5:G5 (related to factor matrix) or any other else?


Assuming we have defined a), if 1st or 2nd options for b), c) & d) are correct, then you could leave only first RefEdit control; if 3rd is valid too, well, then you need the all of them.


Regards!

 

[pvharibabu]

a) It is a contiguous range within C6:O24

b) c) d) I programmed such a way that it picks the start row & columns and end row & columns of the matrix chosen at a). So it is quick to select the whole column for b) and whole rows for c) and d) rather than selecting the associated range. It works even I choose the associated range. So it doesn't really matter. What I mean to say, you need NOT check the range selection for b), c) or d) are associated/related to the range selected for a)


Remove my code for validity of the range selection. You could see the image of what I mean for each RefEdit selection. If you place the cursor in a), you would see the image associated to what selection is required on the right hand side of it. Similarly for others as well.

 

[SirJB7]

Hi, plvharibabu!


Please check if this code does the job:

-----

[pre]

Private Sub LoadFactor_Validation()
' constants
Const ksValidRange = "C6:O24"
' declarations
Dim bError As Boolean, rng As Range
' start
bError = False
' process
With LoadFactor
If IsError(Range(.Text)) Then
bError = True
Else
Set rng = Application.Intersect(Range(.Text), Range(ksValidRange))
If rng Is Nothing Then
bError = True
Else
.Text = rng.Address
End If
End If
End With
' end
Set rng = Nothing
End Sub

[/pre]
-----


Regards!

 

[pvharibabu]

Thanks for your time SirJB7.


The LoadFactor matrix is not constant. It contains any contiguous range as my load cases could go up to 25 and load combinations up to 2000 for a typical structure that I work on.


I am looking for something like this. I tried this from one of the other forums.


------`

Private Sub CommandButton1_Click()

Dim strAddr As String

Dim bIsRange As Boolean


strAddr = LoadFactor.Value

On Error Resume Next

bIsRange = IsObject(Range(strAddr))

On Error GoTo 0


If bIsRange = False Then

MsgBox "Select a valid range for Load Factors Matrix", 48, "Load Factors (Matrix)"

LoadFactor.Value = vbNullString

LoadFactor.SetFocus

Exit Sub

Else

MsgBox Range(LoadFactor).Address(External:=True)

End If

Set rLoadFactor = Range(LoadFactor)


strAddr = CombNo.Value

On Error Resume Next

bIsRange = IsObject(Range(strAddr))

On Error GoTo 0


If bIsRange = False Then

MsgBox "Select a valid range for Load Combinations Column", 48, "Load Combination Numbers (Column)"

CombNo.Value = vbNullString

CombNo.SetFocus

Exit Sub

Else

MsgBox Range(CombNo).Address(External:=True)

End If

Set rCombNo = Range(CombNo)


strAddr = LoadCase.Value

On Error Resume Next

bIsRange = IsObject(Range(strAddr))

On Error GoTo 0

If bIsRange = False Then

MsgBox "Select a valid range for Load Case Titles Row", 48, "Load Case Titles (Row)"

LoadCase.Value = vbNullString

LoadCase.SetFocus

Exit Sub

Else

MsgBox Range(LoadCase).Address(External:=True)

End If

Set rLoadCase = Range(LoadCase)


strAddr = LoadNo.Value

On Error Resume Next

bIsRange = IsObject(Range(strAddr))

On Error GoTo 0

If bIsRange = False Then

MsgBox "Select a valid range for Load Numbers Row", 48, "Load Case Numbers (Row)"

LoadNo.Value = vbNullString

LoadNo.SetFocus

Exit Sub

Else

MsgBox Range(LoadNo).Address(External:=True)

End If

Set rLoadNo = Range(LoadNo)


StartRow = rLoadFactor.Row

EndRow = StartRow + rLoadFactor.Rows.count - 1

StartCol = rLoadFactor.Column

EndCol = StartColumn + rLoadFactor.Columns.count - 1


LoadCombCol = rCombNo.Column

LoadCaseRow = rLoadCase.Row

LoadNoRow = rLoadNo.Row


Unload Me

End Sub

`

------


But the problem with this code is

bIsRange is always [code]True after its first True

, thus the code is not going in to the if[/code] condition.


Can you please help me with this?


Regards

Hari